Vlookup - Display Next Record

[choli0090]

Hi Everyone, I'm trying to figure out a vlookup that will display the first result & in the next cell, display the 2nd result, etc

On One sheet (tab), There can be multiple rows of the same Employee ID & Customer Name. What I'm trying to do is on the "Employee" sheet (tab) is bring back only once instance of Customer name & Subtype, IF the "In Region" column is equal to "Y"

Is this something that can be done, or would a pivot need to be used to collapse the customer name down to one record & apply a filter on "In Region" = Y

Thanks!

Sheet - "Customer"

Emp ID Customer SubType In Region
AAAA AAA A Y
AAAA AAA A Y
AAAA AAA A Y
AAAA BBB A Y
AAAA BBB A Y
AAAA BBB B Y
AAAA BBB B Y
AAAA BBC A N
AAAA BBC A N
BBBB CCC A Y
BBBB CCC A Y
BBBB CCC A Y


Sheet - "Employee"

Emp ID AAAA

Customer SubType
AAA A
BBB A
BBB B

 

[Scott T]

Customer Sheet
[TABLE="class: grid, width: 192"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]A
[/TD]
[TD="width: 64, bgcolor: transparent"]B
[/TD]
[TD="width: 64, bgcolor: transparent"]D
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"]emp ID
[/TD]
[TD="width: 64, bgcolor: transparent"]customer
[/TD]
[TD="width: 64, bgcolor: transparent"]Sub type
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]AAAA
[/TD]
[TD="bgcolor: transparent"]AAA A
[/TD]
[TD="bgcolor: transparent"]Y
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]AAAA
[/TD]
[TD="bgcolor: transparent"]AAA A
[/TD]
[TD="bgcolor: transparent"]Y
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]AAAA
[/TD]
[TD="bgcolor: transparent"]AAA A
[/TD]
[TD="bgcolor: transparent"]Y
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]AAAA
[/TD]
[TD="bgcolor: transparent"]BBB A
[/TD]
[TD="bgcolor: transparent"]Y
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]AAAA
[/TD]
[TD="bgcolor: transparent"]BBB A
[/TD]
[TD="bgcolor: transparent"]Y
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]AAAA
[/TD]
[TD="bgcolor: transparent"]BBB B
[/TD]
[TD="bgcolor: transparent"]Y
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]8[/TD]
[TD="bgcolor: transparent"]AAAA
[/TD]
[TD="bgcolor: transparent"]BBB B
[/TD]
[TD="bgcolor: transparent"]Y
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]9[/TD]
[TD="bgcolor: transparent"]AAAA
[/TD]
[TD="bgcolor: transparent"]BBC A
[/TD]
[TD="bgcolor: transparent"]N
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10[/TD]
[TD="bgcolor: transparent"]AAAA
[/TD]
[TD="bgcolor: transparent"]BBC A
[/TD]
[TD="bgcolor: transparent"]N
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]11[/TD]
[TD="bgcolor: transparent"]BBBB
[/TD]
[TD="bgcolor: transparent"]CCC A
[/TD]
[TD="bgcolor: transparent"]Y
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]12[/TD]
[TD="bgcolor: transparent"]BBBB
[/TD]
[TD="bgcolor: transparent"]CCC A
[/TD]
[TD="bgcolor: transparent"]Y
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]13[/TD]
[TD="bgcolor: transparent"]BBBB
[/TD]
[TD="bgcolor: transparent"]CCC A
[/TD]
[TD="bgcolor: transparent"]Y
[/TD]
[/TR]
</tbody>[/TABLE]


Employee Sheet

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Emp ID[/TD]
[TD]AAA[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Cutomer ID[/TD]
[TD]Subtype[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]AAA A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]BBB A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]BBB A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]BBB C[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In A4 of Employee sheet and copy down
This is an array formula and must be committed with CONTROL+SHIFT+ENTER. If done correctly Excel will put {} around the formula.

=IFERROR(INDEX(Customer!$B$2:$B$13,SMALL(IF(FREQUENCY(IF(ISNUMBER(SEARCH($B$1,Customer!$A$2:$A$13)),MATCH(Customer!$B$2:$B$13,Customer!$B$2:$B$13,0)),ROW(Customer!$B$2:$B$13)-ROW(Customer!$B$1)+1),ROW(Customer!$B$2:$B$13)-ROW(Customer!$B$1)+1),ROWS($A$4:A4))),"")

 

[Marcelo Branco]

Try a pivot table

Something like this

[TABLE="class: grid"]
<tbody>[TR]
[TD]

Emp ID​

[/TD]
[TD]

AAAA​

[/TD]
[/TR]
[TR]
[TD]

Region​

[/TD]
[TD]

Y​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

Customer​

[/TD]
[TD]

SubType​

[/TD]
[/TR]
[TR]
[TD]

AAA​

[/TD]
[TD]

A​

[/TD]
[/TR]
[TR]
[TD]

BBB​

[/TD]
[TD]

A​

[/TD]
[/TR]
[TR]
[TD]

BBB​

[/TD]
[TD]

B​

[/TD]
[/TR]
</tbody>[/TABLE]


Emp ID and Region --> Report Filter
Customer and Sub Type --> Row Labels

Pick Tabular Form in PivotTable Tools > Design > Report Layout
Right-click in Customer field click in Settings > Print & Layout and check Repeat items label checkbox
Remove Grand Total row

M.

 

[choli0090]

Thank you for this. This seems to work, with the exception on how to only have it bring back the customers that belong to that particular Employee. For instance on the "Employee" sheet Cell G1 contains the Employee ID. I want to bring in the customers that the employee sheet!Cell G1 matches on the Customer sheet Row A

 

[choli0090]

Hi Marcelo, Thank you for the suggestion. I've been asked to automate entry into a certain section on the spreadsheet. For instance when they enter in the sales ID on the Employee tab, that particular employee's customers (data source on another tab) would automatically populate.

I'm not sure exactly how a pivot would help with the automation. However I would agree it would help if I was going to "Copy & paste" it from the pivot to the employee tab.

Last, Isn't the "Check Repeat Items Label" an Excel 2010 feature? I'm using 2007

 

[Marcelo Branco]

Hi Marcelo, Thank you for the suggestion. I've been asked to automate entry into a certain section on the spreadsheet. For instance when they enter in the sales ID on the Employee tab, that particular employee's customers (data source on another tab) would automatically populate.

I'm not sure exactly how a pivot would help with the automation. However I would agree it would help if I was going to "Copy & paste" it from the pivot to the employee tab.

Last, Isn't the "Check Repeat Items Label" an Excel 2010 feature? I'm using 2007

You asked about the possibility of using a Pivot Table, so i thought that the users could use it, changing the Report Filter fields as desired.
Yes, "Repeat Items Label" feature is available only in Excel 2010 or more recent versions. Next time, please, tell us your Excel version.
Do you need a formula solution? Scott T has suggested one.

M.