Move row values to columns?

[Anetteqqq]

I have repeating values in column A.
Column B has two or more values that are different from eachother.
I'd like to lift the values from Column B to a new column - to achieve to have column A witout repeating values.

Not sure how else to explain - maybe this image helps?


Capture.GIF

thanks!

[cbatrody]

Hi,

Welcome to the Forum.

Can you post the example file in excel format instead of .jpeg?

[shukla.ankur281190]

Row\Col	A	B	C	D	E	F
2	50001	2	1087	944
3	50001	2	944	944
4	60001	4	911	534	1019	728
5	60001	4	534	534	1019	728
6	60001	4	1019	534	1019	728
7	60001	4	728	534	1019	728
8	70001	2	1048	647
9	70001	2	647	647

D2

Please Login or Register  to view this content.

Copy across

[Glenn Kennedy]

To return a list of uniquer column A values, in E2:

=IFERROR(INDEX($A$2:$A$15,MATCH(0,INDEX(COUNTIF($E$1:$E1,$A$2:$A$15),0),0)),"")

to return the other values, in F2, copied across and down, an array formula:

=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$15=$E2,ROW($A$2:$A$15)),COLUMNS($F2:F2))),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[leelnich]

Hi all. Here's the solution to an identical problem:

Row\Col	A	B	C	D	E	F	G	H	I	J	K
4	Part #	Code		Part #	Codes
5	EE01464	ABC 907 AAA		EE01464	ABC 907 AAA	ABC 907 BBB
6	EE01464	ABC 907 BBB		EE01487	ABC 915 BBB
7	EE01487	ABC 915 BBB		EE01554	ABC 907 AAA	ABC 907 BBB
8	EE01554	ABC 907 AAA
9	EE01554	ABC 907 BBB

This generates a list of unique part #s. Paste in D5 and copy down:

Please Login or Register  to view this content.

...and this finds the codes for each part #. Paste in E5, copy across AND down:

Please Login or Register  to view this content.

[Anetteqqq]

trying it now, computer is a bit slow and old...processing 4000 rows is hard work for it

Thanks - I'm sure it works! Super-clever ppl!

[Glenn Kennedy]

Who are you talking to???

[Anetteqqq]

All of you!

However, I used your file Glenn - and computer is still processing. I will have to copy/paste-special of the first column when it is done to do the array formula. Do you think it will still work? There is no way I can keep the formula in the first column and add new formula - computer will not be able to handle it.

[Glenn Kennedy]

The second formula should be OK. However, identifying unique values will be mightily slow. So... Let's try a different approach.

Open, enabling macros, and hit the button....

My VBA skills are non-existent. I know the second formula will work fine, 'cos I've tried it already. In your real data, you will have to adjust the ranges and drag it to the right until at least one column of blanks appears.