Array to return multiple values against a list of 2000+ items

[jsbaker88]

Hi

I have been struggling with this one and have been trying to use and array. My data is arranged as per the below:

Tab 1
table headers row 3, table data row 4

ID (Column D) Alpha (Column E)
ID123 XYZ
ID146 ABC
ID123 XYZ

This continues for around 50,000 rows. I expect to see each value in column D atleast twice but potentially 10 times.

Tab 2
table headers row 1, table data row 2

ID Alpha 1 Alpha 2 Alpha 3
ID123
ID146
ID145

The IDs in column A are the unique values from the ID column in tab 1. I'd like to enter a formula in alpha 1 (column B) to return the Alpha from Tab 1 for the first time it finds value ID123. I then expect Alpha 2 to be populated with the second alpha value for the second instance of ID123

I'm not sure if this is even possible (ex VBA) with the structure of my workbook. If anyone has any tips it'll be greatly appreciated

Thanks
James

 

[ace19852]

I think I see what you are trying to do which I believe is to make a unique list in Tab2 in column A and across row 1

So assuming you already have a unique list from column A Tab1 in column A Tab2 then

a) Made a unique list from Tab1 column E into say column F,
b) copy that to Tab2 with a transpose paste so now you have a unique column heading where Alpha 1 Alpha 2 etc was then (you can delete the temporary column f in Tab1 now)
c) in Tab2 B2 copy and paste this formula below and drag that over the range.

=IFERROR(IF(INDEX('Tab1'!$B$4:$B$50000,MATCH(1,INDEX(('Tab1'!$A$4:$A$50000=$A2)*('Tab1'!$B$4:$B$50000=B$1),),0))<>"",INDEX('Tab1'!$B$4:$B$50000,MATCH(1,INDEX(('Tab1'!$A$4:$A$50000=$A2)*('Tab1'!$B$4:$B$50000=B$1),),0))),"")

remember to change the 50000 figure in the formula above to the actual row depth.

Cheers