Moving Duplicate Data

[Jaimee001]

I'm fairly new to excel formulas. I have a table that where a region may have more than 1 code which creates duplicates.

Is there a formula in excel where I could move the 2nd code into a new column in the table? or move them to a separate column?

Thanks in advance!!!!! Jaimee (hopefully this makes sense)


this is what I have

region code1

1234 abc

1234 def

5678 abc

9012 abc

9012 ghi

3456 jkl

6789 mno


this is what I'd like

region code1 code2

1234 abc def

5678 abc

9012 abc ghi

3456 jkl

6789 mno

 

[SirJB7]

Hi, Jaimee001!


Give a look at this file:

http://dl.dropbox.com/u/60558749/Moving%20Duplicate%20Data%20%28for%20Jaimee001%20at%20chandoo.org%29.xlsx


First sheet:


The technique used is taken in part (for the unique list) from: http://chandoo.org/wp/2008/11/06/unique-duplicate-missing-items-excel-help/

And in part (list of values associated) from another article of chandoo's website too that I couldn't find.

It uses two auxiliary columns and should be placed at the left of the original table.


Second sheet:


Same thing, but using pivot tables. No aux columns.


Regards!

 

[Jaimee001]

Thank you SirJB7...I have a question. I've been at this for about 4 hours and that's not a problem because that's how I learn. I totally get the formula's in Aux1 and 2 columns. I'm not sure about how the VLookup in the unique column works. It seems to delete out the duplicates which throws off columns F and G. I've put an example out there: http://speedy.sh/d9u3A/Chandoo-Example-for-SIRJB7.xlsx.

I totally appreciate your time on this.

Is the VLookup looking at the pivot table in your example? Thanks in advance!!!!

 

[SirJB7]

Hi, Jaimee001!


First, well for you as you got the formulas in Aux columns and indeed why they should be at left of original data.


Now, VLOOKUP function. Column A just gets numbers from 1 increased by 1 only and each time a new (unique and not repeated) value appears in column C.


Once that is done, VLOOKUP simply searchs for each row value minus 1 (as it starts in row 2), so as to get:

for row 2 the value of column C that has a 1 in column A,

for row 3 the value of column C that has a 2 in column A,

for row 4 the value of column C that has a 3 in column A,

... i.e., all values 1, 2, 3, ... in column A with its related in column C

... so, all unique values of column C.


About my previously uploaded file, there's a mistake in the formulas for columns F in advance:

instead of:

=INDEX($B$2:$D$8,MATCH($C2&F$1,$B$2:$B$8,0),3)

it should have said:

=INDEX($B$2:$D$8,MATCH($E2&F$1,$B$2:$B$8,0),3)

Sorry, my mistake. Please download it again fixed from the same previous link.


About your uploaded file there is a conjugation of two errors:

first, mine as stated above

second, yours while missing to dollar signs:

instead of:

=INDEX($B$2:$D$50,MATCH($C2&F$1,$B2:$B50,0),3)

it should have said:

=INDEX($B$2:$D$50,MATCH($E2&F$1,$B$2:$B$50,0),3)


Because of the lack of the two $ signs, range for row 3 changed to $B3:$B51, for 4 $B4:$B52 and so on. Added to my C by E error.


I re-uploaded your newest file at this link, fixed up to row 25 so you can see the range differences form row 26 in advance:

http://dl.dropbox.com/u/60558749/Moving%20Duplicate%20Data%20-%20Chandoo_Example_for%20SIRJB7%20%28for%20Jaimee001%20at%20chandoo.org%29.xlsx


Hope it helps you, and I apologize for my mistake.


Regards!

 

[Jaimee001]

Thank you! Thank you! Thank you!......totally worked!...I guess I'll be taking the Chandoo.org excel class...need to learn more aboutformulas....again, Thanks so much!

 

[SirJB7]

Hi, tmc.planning!

Glad it solved your issue. Welcome back whenever needed or wanted.

Regards!