Vlookup returns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

Is there a way of getting VLOOKUP to skip the first value it finds and
returns a subsequent one?

I have a worksheet (#1) constructed something like:

Unique ID# ID# Data
A1 1 Unique data 1
A2 2 Unique data 2
A3 3 Unique data 3
A4 2 Unique data 4
A5 2 Unique data 5
A6 1 Unique data 6

Here, the ID# may repeat, but I have no way of knowing how many times, and
therefore do not know how many data I have against each ID#. The unique ID#
does not repeat.

Then there is a second (#2) worksheet constructed something like:

ID# Data
1 Unique data 7
2 Unique data 8
3 Unique data 9

Here, the ID numbers do not repeat.

What I am trying to do is put the unique data from #1 in the same line (but
without overwriting data) as the uniqe data in #2 with the same ID#

The output should look like:

ID# Data(from #2) Data (from #1) Data (from #1) Data
(from#1)

1 Unique data 7 Unique data 1 Unique data 6
2 Unique data 8 Unique data 2 Unique data 4
unique data 5
3 Unique data 9 Unique data 3

Note that the Unique ID# from 1 does not need to be replicated, I have put
it in as a refernce for VLOOKUP. I do not know how many data sets there are
in #1 and none (blank cell) is a valid option.

If there is a better way of doing this than VLOOKUP I am more than happy to
use it :-)

This is in XL 2003, if that makes a difference

TIA

Dave
 
You would need a macro that looks (FINDNEXT) in sh2 for ID in sh1 and puts
the find in the next available column of sh1 row of item.
 
Look here:

http://office.microsoft.com/en-us/excel/HA012260381033.aspx

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi,
|
| Is there a way of getting VLOOKUP to skip the first value it finds and
| returns a subsequent one?
|
| I have a worksheet (#1) constructed something like:
|
| Unique ID# ID# Data
| A1 1 Unique data 1
| A2 2 Unique data 2
| A3 3 Unique data 3
| A4 2 Unique data 4
| A5 2 Unique data 5
| A6 1 Unique data 6
|
| Here, the ID# may repeat, but I have no way of knowing how many times, and
| therefore do not know how many data I have against each ID#. The unique ID#
| does not repeat.
|
| Then there is a second (#2) worksheet constructed something like:
|
| ID# Data
| 1 Unique data 7
| 2 Unique data 8
| 3 Unique data 9
|
| Here, the ID numbers do not repeat.
|
| What I am trying to do is put the unique data from #1 in the same line (but
| without overwriting data) as the uniqe data in #2 with the same ID#
|
| The output should look like:
|
| ID# Data(from #2) Data (from #1) Data (from #1) Data
| (from#1)
|
| 1 Unique data 7 Unique data 1 Unique data 6
| 2 Unique data 8 Unique data 2 Unique data 4
| unique data 5
| 3 Unique data 9 Unique data 3
|
| Note that the Unique ID# from 1 does not need to be replicated, I have put
| it in as a refernce for VLOOKUP. I do not know how many data sets there are
| in #1 and none (blank cell) is a valid option.
|
| If there is a better way of doing this than VLOOKUP I am more than happy to
| use it :-)
|
| This is in XL 2003, if that makes a difference
|
| TIA
|
| Dave
 
Back
Top