Thread: VLookup for multiple values!!

Hello friends my second thread, is related to the first one.

Sheet1 is named "header" Sheet2 is named "detail". The sheet "header" has 8 columns, > 1200 rows (all unique), and detail has 6 columns and >29000 rows (with duplicates). The records in sheet "header" A2:A1200 are unique. Data in each sheet starts in Row 2, with row 1 being the headers for the data.

I used the following formula in the "header" sheet at cell location I2
=VLOOKUP(A2,Detail!$A$2:$F$29497,5,FALSE)
and pulled this formula across >1200 records in sheet "header" to show the data corresponding to A2 in the range $A$1:$F29497 for the 5th column.

The problem I am facing is
The second sheet column E I am looking up has different enteries for a single value of A. How can I display all of those values for each unique value of A of sheet "detail"?
e.g.
Sheet "Header" column A
169255
169226
169247
169248
Sheet "Detail" column A and E
--- A ------ E --
169255 U409A
169255 J237A
169226 J103A
169226 D303A
169226 6108B
169226 1102A
169248 1102A
169248 7862A

Thanks in advance to all of you who take out time to help out other users.
Navneet

Reverse your logic. Use the detail sheet to look up the unique header record
with vlookup. Sort or use a filter to make your data readable.

Lance

"navneetjn" wrote:

>
> Hello friends my second thread, is related to the first one.
>
> Sheet1 is named "header" Sheet2 is named "detail". The sheet "header"
> has 8 columns, > 1200 rows (all unique), and detail has 6 columns and
> >29000 rows (with duplicates). The records in sheet "header" A2:A1200
> are unique. Data in each sheet starts in Row 2, with row 1 being the
> headers for the data.
>
> I used the following formula in the "header" sheet at cell location I2
> =VLOOKUP(A2,DETAIL!$A$2:$F$29497,5,FALSE)
> and pulled this formula across >1200 records in sheet "header" to show
> the data corresponding to A2 in the range $A$1:$F29497 for the 5th
> column.
>
> The problem I am facing is
> The second sheet column E I am looking up has different enteries for a
> single value of A. How can I display all of those values for each
> unique value of A of sheet "detail"?
> e.g.
> Sheet "Header" column A
> 169255
> 169226
> 169247
> 169248
> Sheet "Detail" column A and E
> --- A ------ E --
> 169255 U409A
> 169255 J237A
> 169226 J103A
> 169226 D303A
> 169226 6108B
> 169226 1102A
> 169248 1102A
> 169248 7862A
>
> Thanks in advance to all of you who take out time to help out other
> users.
> Navneet
>
>
> --
> navneetjn
> ------------------------------------------------------------------------
> navneetjn's Profile: http://www.excelforum.com/member.php...o&userid=25330
> View this thread: http://www.excelforum.com/showthread...hreadid=388306
>
>

Yeah that worked. I reversed the logic and got the unique numbers to populate the detail sheet. Then I filtered the column on the N/A and deleted the N/A data.
Voila!!! I have the list that I need.

One drawback

eg The result I am getting is in the following format (A big print job)
-+---A----+---B-----+
1| 16196 | L1610 |
-+---------+---------+
2| 16196 | L2659 |
-+---------+---------+
3| 16196 | B254C |
-+---------+---------+
Instead of
-+----A---+----B----+---C---+---D----+
1| 16196 | L1610 | L2659 | B254C |
-+---------+---------+--------+---------+

Where ABCD are the column numbers and 123 are the row numbers. That is ok as long as I get the result

Thanks a lot.


[QUOTE=LanceB]Reverse your logic. Use the detail sheet to look up the unique header record
with vlookup. Sort or use a filter to make your data readable.

Lance

On your sheet called 'Header', enter the following formula in B2, copy across and down:

=IF(COLUMNS($B2:B2)<=COUNTIF(Detail!$A$2:$A$8,$A2),INDEX(Detail!$E$2:$E$8,SMALL(IF(Detail!$A$2:$A$8= $A2,ROW(Detail!$E$2:$E$8)-ROW(Detail!$E$2)+1),COLUMNS($B2:B2))),"")
...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

Originally Posted by navneetjn

Hello friends my second thread, is related to the first one.

Sheet1 is named "header" Sheet2 is named "detail". The sheet "header" has 8 columns, > 1200 rows (all unique), and detail has 6 columns and >29000 rows (with duplicates). The records in sheet "header" A2:A1200 are unique. Data in each sheet starts in Row 2, with row 1 being the headers for the data.

I used the following formula in the "header" sheet at cell location I2
=VLOOKUP(A2,Detail!$A$2:$F$29497,5,FALSE)
and pulled this formula across >1200 records in sheet "header" to show the data corresponding to A2 in the range $A$1:$F29497 for the 5th column.

The problem I am facing is
The second sheet column E I am looking up has different enteries for a single value of A. How can I display all of those values for each unique value of A of sheet "detail"?
e.g.
Sheet "Header" column A
169255
169226
169247
169248
Sheet "Detail" column A and E
--- A ------ E --
169255 U409A
169255 J237A
169226 J103A
169226 D303A
169226 6108B
169226 1102A
169248 1102A
169248 7862A

Thanks in advance to all of you who take out time to help out other users.
Navneet

Reverse your logic. Use the detail sheet to look up the unique header record
with vlookup. Sort or use a filter to make your data readable.

Lance

"navneetjn" wrote:

>
> Hello friends my second thread, is related to the first one.
>
> Sheet1 is named "header" Sheet2 is named "detail". The sheet "header"
> has 8 columns, > 1200 rows (all unique), and detail has 6 columns and
> >29000 rows (with duplicates). The records in sheet "header" A2:A1200
> are unique. Data in each sheet starts in Row 2, with row 1 being the
> headers for the data.
>
> I used the following formula in the "header" sheet at cell location I2
> =VLOOKUP(A2,DETAIL!$A$2:$F$29497,5,FALSE)
> and pulled this formula across >1200 records in sheet "header" to show
> the data corresponding to A2 in the range $A$1:$F29497 for the 5th
> column.
>
> The problem I am facing is
> The second sheet column E I am looking up has different enteries for a
> single value of A. How can I display all of those values for each
> unique value of A of sheet "detail"?
> e.g.
> Sheet "Header" column A
> 169255
> 169226
> 169247
> 169248
> Sheet "Detail" column A and E
> --- A ------ E --
> 169255 U409A
> 169255 J237A
> 169226 J103A
> 169226 D303A
> 169226 6108B
> 169226 1102A
> 169248 1102A
> 169248 7862A
>
> Thanks in advance to all of you who take out time to help out other
> users.
> Navneet
>
>
> --
> navneetjn
> ------------------------------------------------------------------------
> navneetjn's Profile: http://www.excelforum.com/member.php...o&userid=25330
> View this thread: http://www.excelforum.com/showthread...hreadid=388306
>
>

Reverse your logic. Use the detail sheet to look up the unique header record
with vlookup. Sort or use a filter to make your data readable.

Lance

"navneetjn" wrote:

>
> Hello friends my second thread, is related to the first one.
>
> Sheet1 is named "header" Sheet2 is named "detail". The sheet "header"
> has 8 columns, > 1200 rows (all unique), and detail has 6 columns and
> >29000 rows (with duplicates). The records in sheet "header" A2:A1200
> are unique. Data in each sheet starts in Row 2, with row 1 being the
> headers for the data.
>
> I used the following formula in the "header" sheet at cell location I2
> =VLOOKUP(A2,DETAIL!$A$2:$F$29497,5,FALSE)
> and pulled this formula across >1200 records in sheet "header" to show
> the data corresponding to A2 in the range $A$1:$F29497 for the 5th
> column.
>
> The problem I am facing is
> The second sheet column E I am looking up has different enteries for a
> single value of A. How can I display all of those values for each
> unique value of A of sheet "detail"?
> e.g.
> Sheet "Header" column A
> 169255
> 169226
> 169247
> 169248
> Sheet "Detail" column A and E
> --- A ------ E --
> 169255 U409A
> 169255 J237A
> 169226 J103A
> 169226 D303A
> 169226 6108B
> 169226 1102A
> 169248 1102A
> 169248 7862A
>
> Thanks in advance to all of you who take out time to help out other
> users.
> Navneet
>
>
> --
> navneetjn
> ------------------------------------------------------------------------
> navneetjn's Profile: http://www.excelforum.com/member.php...o&userid=25330
> View this thread: http://www.excelforum.com/showthread...hreadid=388306
>
>

Reverse your logic. Use the detail sheet to look up the unique header record
with vlookup. Sort or use a filter to make your data readable.

Lance

"navneetjn" wrote:

>
> Hello friends my second thread, is related to the first one.
>
> Sheet1 is named "header" Sheet2 is named "detail". The sheet "header"
> has 8 columns, > 1200 rows (all unique), and detail has 6 columns and
> >29000 rows (with duplicates). The records in sheet "header" A2:A1200
> are unique. Data in each sheet starts in Row 2, with row 1 being the
> headers for the data.
>
> I used the following formula in the "header" sheet at cell location I2
> =VLOOKUP(A2,DETAIL!$A$2:$F$29497,5,FALSE)
> and pulled this formula across >1200 records in sheet "header" to show
> the data corresponding to A2 in the range $A$1:$F29497 for the 5th
> column.
>
> The problem I am facing is
> The second sheet column E I am looking up has different enteries for a
> single value of A. How can I display all of those values for each
> unique value of A of sheet "detail"?
> e.g.
> Sheet "Header" column A
> 169255
> 169226
> 169247
> 169248
> Sheet "Detail" column A and E
> --- A ------ E --
> 169255 U409A
> 169255 J237A
> 169226 J103A
> 169226 D303A
> 169226 6108B
> 169226 1102A
> 169248 1102A
> 169248 7862A
>
> Thanks in advance to all of you who take out time to help out other
> users.
> Navneet
>
>
> --
> navneetjn
> ------------------------------------------------------------------------
> navneetjn's Profile: http://www.excelforum.com/member.php...o&userid=25330
> View this thread: http://www.excelforum.com/showthread...hreadid=388306
>
>

Reverse your logic. Use the detail sheet to look up the unique header record
with vlookup. Sort or use a filter to make your data readable.

Lance

"navneetjn" wrote:

>
> Hello friends my second thread, is related to the first one.
>
> Sheet1 is named "header" Sheet2 is named "detail". The sheet "header"
> has 8 columns, > 1200 rows (all unique), and detail has 6 columns and
> >29000 rows (with duplicates). The records in sheet "header" A2:A1200
> are unique. Data in each sheet starts in Row 2, with row 1 being the
> headers for the data.
>
> I used the following formula in the "header" sheet at cell location I2
> =VLOOKUP(A2,DETAIL!$A$2:$F$29497,5,FALSE)
> and pulled this formula across >1200 records in sheet "header" to show
> the data corresponding to A2 in the range $A$1:$F29497 for the 5th
> column.
>
> The problem I am facing is
> The second sheet column E I am looking up has different enteries for a
> single value of A. How can I display all of those values for each
> unique value of A of sheet "detail"?
> e.g.
> Sheet "Header" column A
> 169255
> 169226
> 169247
> 169248
> Sheet "Detail" column A and E
> --- A ------ E --
> 169255 U409A
> 169255 J237A
> 169226 J103A
> 169226 D303A
> 169226 6108B
> 169226 1102A
> 169248 1102A
> 169248 7862A
>
> Thanks in advance to all of you who take out time to help out other
> users.
> Navneet
>
>
> --
> navneetjn
> ------------------------------------------------------------------------
> navneetjn's Profile: http://www.excelforum.com/member.php...o&userid=25330
> View this thread: http://www.excelforum.com/showthread...hreadid=388306
>
>

Reverse your logic. Use the detail sheet to look up the unique header record
with vlookup. Sort or use a filter to make your data readable.

Lance

"navneetjn" wrote:

>
> Hello friends my second thread, is related to the first one.
>
> Sheet1 is named "header" Sheet2 is named "detail". The sheet "header"
> has 8 columns, > 1200 rows (all unique), and detail has 6 columns and
> >29000 rows (with duplicates). The records in sheet "header" A2:A1200
> are unique. Data in each sheet starts in Row 2, with row 1 being the
> headers for the data.
>
> I used the following formula in the "header" sheet at cell location I2
> =VLOOKUP(A2,DETAIL!$A$2:$F$29497,5,FALSE)
> and pulled this formula across >1200 records in sheet "header" to show
> the data corresponding to A2 in the range $A$1:$F29497 for the 5th
> column.
>
> The problem I am facing is
> The second sheet column E I am looking up has different enteries for a
> single value of A. How can I display all of those values for each
> unique value of A of sheet "detail"?
> e.g.
> Sheet "Header" column A
> 169255
> 169226
> 169247
> 169248
> Sheet "Detail" column A and E
> --- A ------ E --
> 169255 U409A
> 169255 J237A
> 169226 J103A
> 169226 D303A
> 169226 6108B
> 169226 1102A
> 169248 1102A
> 169248 7862A
>
> Thanks in advance to all of you who take out time to help out other
> users.
> Navneet
>
>
> --
> navneetjn
> ------------------------------------------------------------------------
> navneetjn's Profile: http://www.excelforum.com/member.php...o&userid=25330
> View this thread: http://www.excelforum.com/showthread...hreadid=388306
>
>

Reverse your logic. Use the detail sheet to look up the unique header record
with vlookup. Sort or use a filter to make your data readable.

Lance

"navneetjn" wrote:

>
> Hello friends my second thread, is related to the first one.
>
> Sheet1 is named "header" Sheet2 is named "detail". The sheet "header"
> has 8 columns, > 1200 rows (all unique), and detail has 6 columns and
> >29000 rows (with duplicates). The records in sheet "header" A2:A1200
> are unique. Data in each sheet starts in Row 2, with row 1 being the
> headers for the data.
>
> I used the following formula in the "header" sheet at cell location I2
> =VLOOKUP(A2,DETAIL!$A$2:$F$29497,5,FALSE)
> and pulled this formula across >1200 records in sheet "header" to show
> the data corresponding to A2 in the range $A$1:$F29497 for the 5th
> column.
>
> The problem I am facing is
> The second sheet column E I am looking up has different enteries for a
> single value of A. How can I display all of those values for each
> unique value of A of sheet "detail"?
> e.g.
> Sheet "Header" column A
> 169255
> 169226
> 169247
> 169248
> Sheet "Detail" column A and E
> --- A ------ E --
> 169255 U409A
> 169255 J237A
> 169226 J103A
> 169226 D303A
> 169226 6108B
> 169226 1102A
> 169248 1102A
> 169248 7862A
>
> Thanks in advance to all of you who take out time to help out other
> users.
> Navneet
>
>
> --
> navneetjn
> ------------------------------------------------------------------------
> navneetjn's Profile: http://www.excelforum.com/member.php...o&userid=25330
> View this thread: http://www.excelforum.com/showthread...hreadid=388306
>
>

Reverse your logic. Use the detail sheet to look up the unique header record
with vlookup. Sort or use a filter to make your data readable.

Lance

"navneetjn" wrote:

>
> Hello friends my second thread, is related to the first one.
>
> Sheet1 is named "header" Sheet2 is named "detail". The sheet "header"
> has 8 columns, > 1200 rows (all unique), and detail has 6 columns and
> >29000 rows (with duplicates). The records in sheet "header" A2:A1200
> are unique. Data in each sheet starts in Row 2, with row 1 being the
> headers for the data.
>
> I used the following formula in the "header" sheet at cell location I2
> =VLOOKUP(A2,DETAIL!$A$2:$F$29497,5,FALSE)
> and pulled this formula across >1200 records in sheet "header" to show
> the data corresponding to A2 in the range $A$1:$F29497 for the 5th
> column.
>
> The problem I am facing is
> The second sheet column E I am looking up has different enteries for a
> single value of A. How can I display all of those values for each
> unique value of A of sheet "detail"?
> e.g.
> Sheet "Header" column A
> 169255
> 169226
> 169247
> 169248
> Sheet "Detail" column A and E
> --- A ------ E --
> 169255 U409A
> 169255 J237A
> 169226 J103A
> 169226 D303A
> 169226 6108B
> 169226 1102A
> 169248 1102A
> 169248 7862A
>
> Thanks in advance to all of you who take out time to help out other
> users.
> Navneet
>
>
> --
> navneetjn
> ------------------------------------------------------------------------
> navneetjn's Profile: http://www.excelforum.com/member.php...o&userid=25330
> View this thread: http://www.excelforum.com/showthread...hreadid=388306
>
>

Reverse your logic. Use the detail sheet to look up the unique header record
with vlookup. Sort or use a filter to make your data readable.

Lance

"navneetjn" wrote:

>
> Hello friends my second thread, is related to the first one.
>
> Sheet1 is named "header" Sheet2 is named "detail". The sheet "header"
> has 8 columns, > 1200 rows (all unique), and detail has 6 columns and
> >29000 rows (with duplicates). The records in sheet "header" A2:A1200
> are unique. Data in each sheet starts in Row 2, with row 1 being the
> headers for the data.
>
> I used the following formula in the "header" sheet at cell location I2
> =VLOOKUP(A2,DETAIL!$A$2:$F$29497,5,FALSE)
> and pulled this formula across >1200 records in sheet "header" to show
> the data corresponding to A2 in the range $A$1:$F29497 for the 5th
> column.
>
> The problem I am facing is
> The second sheet column E I am looking up has different enteries for a
> single value of A. How can I display all of those values for each
> unique value of A of sheet "detail"?
> e.g.
> Sheet "Header" column A
> 169255
> 169226
> 169247
> 169248
> Sheet "Detail" column A and E
> --- A ------ E --
> 169255 U409A
> 169255 J237A
> 169226 J103A
> 169226 D303A
> 169226 6108B
> 169226 1102A
> 169248 1102A
> 169248 7862A
>
> Thanks in advance to all of you who take out time to help out other
> users.
> Navneet
>
>
> --
> navneetjn
> ------------------------------------------------------------------------
> navneetjn's Profile: http://www.excelforum.com/member.php...o&userid=25330
> View this thread: http://www.excelforum.com/showthread...hreadid=388306
>
>

Reverse your logic. Use the detail sheet to look up the unique header record
with vlookup. Sort or use a filter to make your data readable.

Lance

"navneetjn" wrote:

>
> Hello friends my second thread, is related to the first one.
>
> Sheet1 is named "header" Sheet2 is named "detail". The sheet "header"
> has 8 columns, > 1200 rows (all unique), and detail has 6 columns and
> >29000 rows (with duplicates). The records in sheet "header" A2:A1200
> are unique. Data in each sheet starts in Row 2, with row 1 being the
> headers for the data.
>
> I used the following formula in the "header" sheet at cell location I2
> =VLOOKUP(A2,DETAIL!$A$2:$F$29497,5,FALSE)
> and pulled this formula across >1200 records in sheet "header" to show
> the data corresponding to A2 in the range $A$1:$F29497 for the 5th
> column.
>
> The problem I am facing is
> The second sheet column E I am looking up has different enteries for a
> single value of A. How can I display all of those values for each
> unique value of A of sheet "detail"?
> e.g.
> Sheet "Header" column A
> 169255
> 169226
> 169247
> 169248
> Sheet "Detail" column A and E
> --- A ------ E --
> 169255 U409A
> 169255 J237A
> 169226 J103A
> 169226 D303A
> 169226 6108B
> 169226 1102A
> 169248 1102A
> 169248 7862A
>
> Thanks in advance to all of you who take out time to help out other
> users.
> Navneet
>
>
> --
> navneetjn
> ------------------------------------------------------------------------
> navneetjn's Profile: http://www.excelforum.com/member.php...o&userid=25330
> View this thread: http://www.excelforum.com/showthread...hreadid=388306
>
>

Reverse your logic. Use the detail sheet to look up the unique header record
with vlookup. Sort or use a filter to make your data readable.

Lance

"navneetjn" wrote:

>
> Hello friends my second thread, is related to the first one.
>
> Sheet1 is named "header" Sheet2 is named "detail". The sheet "header"
> has 8 columns, > 1200 rows (all unique), and detail has 6 columns and
> >29000 rows (with duplicates). The records in sheet "header" A2:A1200
> are unique. Data in each sheet starts in Row 2, with row 1 being the
> headers for the data.
>
> I used the following formula in the "header" sheet at cell location I2
> =VLOOKUP(A2,DETAIL!$A$2:$F$29497,5,FALSE)
> and pulled this formula across >1200 records in sheet "header" to show
> the data corresponding to A2 in the range $A$1:$F29497 for the 5th
> column.
>
> The problem I am facing is
> The second sheet column E I am looking up has different enteries for a
> single value of A. How can I display all of those values for each
> unique value of A of sheet "detail"?
> e.g.
> Sheet "Header" column A
> 169255
> 169226
> 169247
> 169248
> Sheet "Detail" column A and E
> --- A ------ E --
> 169255 U409A
> 169255 J237A
> 169226 J103A
> 169226 D303A
> 169226 6108B
> 169226 1102A
> 169248 1102A
> 169248 7862A
>
> Thanks in advance to all of you who take out time to help out other
> users.
> Navneet
>
>
> --
> navneetjn
> ------------------------------------------------------------------------
> navneetjn's Profile: http://www.excelforum.com/member.php...o&userid=25330
> View this thread: http://www.excelforum.com/showthread...hreadid=388306
>
>

Reverse your logic. Use the detail sheet to look up the unique header record
with vlookup. Sort or use a filter to make your data readable.

Lance

"navneetjn" wrote:

>
> Hello friends my second thread, is related to the first one.
>
> Sheet1 is named "header" Sheet2 is named "detail". The sheet "header"
> has 8 columns, > 1200 rows (all unique), and detail has 6 columns and
> >29000 rows (with duplicates). The records in sheet "header" A2:A1200
> are unique. Data in each sheet starts in Row 2, with row 1 being the
> headers for the data.
>
> I used the following formula in the "header" sheet at cell location I2
> =VLOOKUP(A2,DETAIL!$A$2:$F$29497,5,FALSE)
> and pulled this formula across >1200 records in sheet "header" to show
> the data corresponding to A2 in the range $A$1:$F29497 for the 5th
> column.
>
> The problem I am facing is
> The second sheet column E I am looking up has different enteries for a
> single value of A. How can I display all of those values for each
> unique value of A of sheet "detail"?
> e.g.
> Sheet "Header" column A
> 169255
> 169226
> 169247
> 169248
> Sheet "Detail" column A and E
> --- A ------ E --
> 169255 U409A
> 169255 J237A
> 169226 J103A
> 169226 D303A
> 169226 6108B
> 169226 1102A
> 169248 1102A
> 169248 7862A
>
> Thanks in advance to all of you who take out time to help out other
> users.
> Navneet
>
>
> --
> navneetjn
> ------------------------------------------------------------------------
> navneetjn's Profile: http://www.excelforum.com/member.php...o&userid=25330
> View this thread: http://www.excelforum.com/showthread...hreadid=388306
>
>