Using VLookup and offset to fill columns

[Gr8tDaze]

Hope someone could help, I thought I had this but …

I have 2 columns: Part# and Code#. The Part# can have up to 30 code# s attributed. I need to go down list and pull all code#s for each different part# and place code in a columns (Code1, Code2, Code 3, etc).

Example:

Part# Code#

1234A AAAF
1234A AAFF
1234A AFFF
6789X ZZZA
6789X ZZAA
8777N PPPP
8777N PPPX
8777N PPXX
8777N PXXX
8777N XXXX

[B/]Need to populate codes across columns:[/B}

Part# Code1 Code2 Code3 Code4 Code5 etc.
1234A AAAF AAFF AFFF FFFF
6789X ZZZA ZZAA
8777N PPPP PPPX PPXX PXXXX XXXXX

I tried this:

=Offset(VLOOKUP(IF($A2=$A2,$A2,OFFSET($A2,1,0)),Code,2,FALSE),0,1)

I think I going in the right direction but not there. I’d appreciate any help, thanks!
~Gr8tDaze!

[Tony Valko]

Try this...


Data Range

	A	B	C	D	E	F	G	H	I	J
1
2	1234A	AAAF		1234A	AAAF	AAFF	AFFF
3	1234A	AAFF		6789X	ZZZA	ZZAA
4	1234A	AFFF		8777N	PPPP	PPPX	PPXX	PXXX	XXXX
5	6789X	ZZZA
6	6789X	ZZAA
7	8777N	PPPP
8	8777N	PPPX
9	8777N	PPXX
10	8777N	PXXX
11	8777N	XXXX

Enter this formula in E2:

=IF(COLUMNS($E2:E2)>COUNTIF($A$2:$A$11,$D2),"",INDEX($B$2:$B$11,MATCH($D2,$A$2:$A$11,0)+COLUMNS($E2:E2)-1))

Copy down as needed then across until you get a column full of blanks.

[AlKey]

Hi Gr8tDaze and welcome to the forum

Please see attached file.

[Gr8tDaze]

Hi AlKey,

Thank you soooo much AlKey. Regret the long delay to respond (pulled to another project). Your code works great. My issue is trying to get code calculated through 170K records. Having very difficult time as system freezes up. Do you think there would be better performance with a pivot table or vba code? (Again, sorry for the late thank you).

[Gr8tDaze]

Thanks Tony! Sorry for the delay in response I go pulled to another project. For some reason I could not get this code to work but was able to use AlKey's code below. Now having problem getting this to work for doing calculation on 170K rows ... Excel freezes. Is this too complex for a pivot table or would I get better performance with vba? (I asked the same question in response to AlKey .. I hope I'm not duplicating efforts)

[AlKey]

Gr8tDaze,

I really don't know if the Pivot table or VBA solution will perform faster than formulas. 170k of date is a lot. The system freeze may have to to with your system resources. Sorry if I couldn't provide a better answer.

[Gr8tDaze]

Thanks again AlKey ... assumed problem with systems resources. I really appreciate your help with this.

[AlKey]

You're Welcome. Don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).