Vlookup transpose value, but don't repeat.

[I.am.Rustam]

Please advise with this:

A1: 1
A2: 2
A3: 3
A4: 1
A5: 2
A6: 7
A7: 1
etc..

B1: 5K
B2: 2K
B3: 3.7K
B4: 4K
B5: 2K
B6: 4.5K
B7: 110K
etc....


I need to transpose values from column B to column C.

If A1: 1 I need to take value from B:1 and place it in C1. If number 1 repeats in Column A (A5), than I need to paste value of B (B5) to D1 etc....

So in above example, I would have:

C1: 5K, D1: 4K, E1: 110K etc...


Please help!

Thanks a lot!

[alansidman]

Try this:

Please Login or Register  to view this content.

[I.am.Rustam]

Alan,

Thanks for your response. I tried this code, but it didn't work. I tried to modify it a bit, it worked, but only getting the last value skipping all the previous.

Please advise. Thanks

[mehmetcik]

Ok insert an empty row above your data.

Select the formula tab select name manager select new

enter nn as the name

enter

Please Login or Register  to view this content.

in the refers to box and close the name manager

enter this formula into cell C2 using ctrl shift and enter

Please Login or Register  to view this content.

Fill this down until you get a blank cell

enter this formula into cell D2 using ctrl shift and enter

Please Login or Register  to view this content.

fill right and down until you get all blanks

[alansidman]

Worked for me. Attach a copy of your worksheet for testing.

[newdoverman]

Enter this in C1 and fill across:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

	A	B	C	D	E
1	1	5k	5k	4k	110k
2	2	2k
3	3	3.7k
4	1	4k
5	2	2k
6	7	4.5k
7	1	110k

[newdoverman]

Alan's code runs perfectly. Copy the code, right click on the worksheet tab with the data, click on View Code, Paste the code into the module and click on the red X to exit the VBA editor. Go to the Developer tab, click on Macros, select the macro with "ams" and run.

[FlameRetired]

Another way

Please Login or Register  to view this content.

Row\Col	A	B	C	D	E	F
1	1	5K	5K	4K	110K	In C1:E1 =IFERROR(INDEX($B$1:$B$7,(COLUMNS($A:A)-1)*3+1),"")
2	2	2K
3	3	3.7K
4	1	4K
5	2	2K
6	7	4.5K
7	1	110K

[I.am.Rustam]

Guys,

Sorry for the late response! Thank you very much, I tried all of your ways, all of them work great, including Alan's macro.

Thanks again for your help guys! Greatly appreciate it!!!! Reputation to all of you guys!

Rus

[FlameRetired]

Glad they worked. Thanks for the feedback and rep.