How Do You Align Duplicate Data Onto The Same Row And Bring Along The Values?

[Randy Martins]

Greetings

I am not very good with excel, could someone please help me. I am looking for a simple formula, that will align same name data found in different columns, into one row, but also bring their values along as well.

See attached excel sheet

I am already familiar with the following formula =IF(ISNA(MATCH(A1,C:C,0)),"",INDEX(C:C,MATCH(A1,C:C,0)))

But this only works for the names, it does not bring along the values as well.

Your help would be much appreciated, and Note I am still beginner level in excel

[ChemistB]

Okay, you need to know what part of the formula does what. The part in red here is what returns values so that needs to be pointed at the column you want returned

=IF(ISNA(MATCH(A1,C:C,0)),"",INDEX(C:C,MATCH(A1,C:C,0)))

Next, it I tightened it up so it's only looking at the range it's pulling from (rows 2 to 14). I then needed to add anchors ($) so that the cell references didn't change as I copied them down
In C18 copied down

=IF(ISNA(MATCH($A18,$C$2:$C$14,0)),"",INDEX(C$2:C$14,MATCH($A18,$C$2:$C$14,0)))

Then in D18, we will still be using the names to find the proper rows but we'll be returning the values in Column D so D18 looks like this

=IF(ISNA(MATCH($A18,$C$2:$C$14,0)),"",INDEX(D$2:D$14,MATCH($A18,$C$2:$C$14,0)))

Note: I fixed columns A and B because you stopped at M, also removed C value (but kept C because we use that to look up things in columnd C:F
Questions?

[Randy Martins]

Thanks for the quick response ChemistB, let me try this out.

[messytess]

I am attempting to do the exact same thing but your instructions are unclear; I end up with either a 0 or a circular referenc error...

[ChemistB]

I am attempting to do the exact same thing but your instructions are unclear; I end up with either a 0 or a circular referenc error...

Circular reference errors might be if you are trying to input the formulas in Randy's first table. Are you using these formula's in Randy's spreadsheet or trying it in a spreadsheet of your own?

[Randy Martins]

Okay, you need to know what part of the formula does what. The part in red here is what returns values so that needs to be pointed at the column you want returned

=IF(ISNA(MATCH(A1,C:C,0)),"",INDEX(C:C,MATCH(A1,C:C,0)))

Next, it I tightened it up so it's only looking at the range it's pulling from (rows 2 to 14). I then needed to add anchors ($) so that the cell references didn't change as I copied them down
In C18 copied down

=IF(ISNA(MATCH($A18,$C$2:$C$14,0)),"",INDEX(C$2:C$14,MATCH($A18,$C$2:$C$14,0)))

Then in D18, we will still be using the names to find the proper rows but we'll be returning the values in Column D so D18 looks like this

=IF(ISNA(MATCH($A18,$C$2:$C$14,0)),"",INDEX(D$2:D$14,MATCH($A18,$C$2:$C$14,0)))

Note: I fixed columns A and B because you stopped at M, also removed C value (but kept C because we use that to look up things in columnd C:F
Questions?

OK ChemistB I'm still a bit confused. I have attached another sample excel sheet. Could you please work on this with the formulas you are suggesting.

[shg]

The workbook at https://app.box.com/shared/elrnbidnr7 has a macro that will do that.

[ChemistB]

I copied A1:A14 down to A17 and then sorted them alphabetically.
Then in B18 copied down

=INDEX($B$2:$B$14, MATCH(A18, $A$2:$A$14,0))

In C18 copied down

=IFERROR(INDEX($C$2:$C$14, MATCH(A18, $C$2:$C$14,0)),"")

In D18 copied down


=IFERROR(INDEX($D$2:$D$14, MATCH(A18, $C$2:$C$14,0)),"")
Questions?

[Randy Martins]

I copied A1:A14 down to A17 and then sorted them alphabetically.
Then in B18 copied down

=INDEX($B$2:$B$14, MATCH(A18, $A$2:$A$14,0))

In C18 copied down

=IFERROR(INDEX($C$2:$C$14, MATCH(A18, $C$2:$C$14,0)),"")

In D18 copied down


=IFERROR(INDEX($D$2:$D$14, MATCH(A18, $C$2:$C$14,0)),"")
Questions?

Hi ChemistB you did it. This is perfect, exactly what I was looking for.

Thank you so much. God Bless You

[Randy Martins]

Will Admin edit and put solved in the post title or am I supposed to do that???

[ChemistB]

Go to the top post and I think it's under "Thread Tools"

[Randy Martins]

Great, thanks once again

[Randy Martins]

Hi Chemist B

I used your formula and it works great but then I realized it's only putting the duplicates in the new column what about the non duplicate data?

How do I get the non duplicate data to appear in the column as well?

I have attached another sample sheet that now includes some names that are not duplicates. Could you please work your magic on this, so I can see how you would do it.

[shg]

The workbook at the link would convert that to this:

Row\Col	A	B	C	D
1	NAME	AMOUNT	NAME	AMOUNT
2	Abigail	5,000,000.00
3	Adam	212,182.62	Adam	1,000,000.00
4			Alan	234,765.00
5	Andrew	140,148.63	Andrew	6,500,000.00
6	Barry	5,000,000.00	Barry	10,500,000.00
7	Delia	250,000.00
8			George	235,675.00
9	John	212,182.62	John	3,333,333.44
10	Jordan	1,745,478.21	Jordan	3,000,000.00
11	Keith	250,000.00	Keith	325,632.00
12	Kimberly	5,000,000.00	Kimberly	574,300.00
13	Linda	267,000.00
14			Madonna	200,000.00
15	Martin	140,148.63	Martin	300,000.00
16	Michael	-	Michael	225,555.67
17	Michelle	21,333,118.29	Michelle	235,675.00
18	Patricia	700,743.15	Patricia	574,300.00
19	Patrick	156,750.00	Patrick	234,765.00
20	Samantha	250,000.00
21	Stacey	711,955.03	Stacey	4,563,258.13
22	Thomas	151,360.51	Thomas	560,000.00
23	Trisha	156,750.00
24	Zachary	156,750.00

[ChemistB]

In order to get things in alphabetical order, I suggest the macro suggested by SHG in post #7. My formulas get very convoluted at this point.

[Randy Martins]

Hi Thanks for the help Shg but at my excel level I am having trouble understanding how to go about using the macro. Is there any way you can explain it at excel beginner level?

[shg]

o Open both workbooks

o Select Sheet2 in your workbook

o Select A1 and C1

o In the Names box, left of the formula bar, enter Sheet2!Keys

o Alt+F8, run macro AlignKeys

[Randy Martins]

OK Let me give it a shot, thanks

[shg]

You're welcome.

[Randy Martins]

o Open both workbooks

o Select Sheet2 in your workbook

o Select A1 and C1

o In the Names box, left of the formula bar, enter Sheet2!Keys

o Alt+F8, run macro AlignKeys

Hi Shg

I'm back, I got lost with another job and haven't tackled this yet. As I said I have basic excel knowledge and I do not even know what Macros are. I opened the Aligns Key Demo you sent but I don't understand it and then in your pointers you start by saying open both workbooks confused which work books.

Sorry to sound like such a baby but please explain to me step by step from opening the Aligns Key Demo.

attached is a sample excel sheet with data that I need to sort.

[shg]

Open the AlignKeys worbook.

Open your workbook.

Select the 6 cells containing the months, and paste 'Sort List'!keys in the Names box, left of the formula bar.

Do Alt+F8 to open the macros dialog. In the Macro name box, paste AlignKeys. Press Run.

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	JULY			AUGUST			SEPTEMBER			OCTOBER			NOVEMBER			DECEMBER
3	Alana	449,510.26		Alana	1,123,775.65		Alana	55,000.00		Alana	1,530,023.22		Alana	455,974.10		Alana	122,404.88
4	Alex	457,629.37		Alex	224,755.13		Alex	735,419.29		Alex	899,020.52		Alex	224,755.13		Alex	122,404.88
5	Andrew	88,302.22		Andrew	682,384.50		Andrew	83,222.91		Andrew	122,404.88		Andrew	95,626.12		Andrew	92,034.00
6	Angela	94,591.96		Angela	69,546.51		Angela	83,222.91					Angela	674,265.39
7	Bartholomew	224,755.13		Bartholomew	88,302.22		Bartholomew	674,265.39		Bartholomew	224,755.13		Bartholomew	232,874.24
8							Ben	234,111.33								Ben	465,748.48
9	Catherine	18,406.80		Catherine	232,874.24		Catherine	176,478.08		Catherine	211,428.56		Catherine	1,460,476.71
10	Ceceile	698,622.72		Ceceile	122,404.88		Ceceile	130,893.53					Ceceile	202,267.22
11										Chino	55,220.40
12										Corinne	224,755.13
13										Dana	130,893.53					Dana	224,755.13
14	David	91,194.82		David	176,604.44		David	55,000.00		David	224,755.13		David	449,510.26
15	Gertrude	461,845.66		Gertrude	674,265.39		Gertrude	674,265.39		Gertrude	224,755.13		Gertrude	465,748.48		Gertrude	1,599,569.73
16	Godfrey	244,809.76		Godfrey	224,755.13		Godfrey	70,943.97					Godfrey	533,152.07
17										Gregory	119,162.93
18	Jacob	63,675.04		Jacob	94,591.96		Jacob	489,619.52		Jacob	224,755.13		Jacob	119,417.28
19	Jane	119,417.28		Jane	889,946.43		Jane	224,755.13		Jane	381,127.70		Jane	224,755.13
20																Jeremy	224,755.13
21										Jobeth	367,214.64
22										Joseph	224,755.13					Joseph	364,779.28
23	Kevin	224,755.13		Kevin	88,302.22		Kevin	461,845.66		Kevin	119,417.28		Kevin	321,879.48		Kevin	41,250.00
24																Kimberly	1,581,405.02
25	Martin	449,510.26		Martin	1,321,383.69		Martin	215,681.04		Martin	1,530,023.22		Martin	187,058.72		Martin	232,874.24
26										Matthew	461,845.66					Matthew	312,300.00
27	Michelle	973,651.14		Michelle	13,750.00		Michelle	-		Michelle	498,108.17		Michelle	674,265.39		Michelle	122,404.88
28	Nina	224,755.13		Nina	224,755.13					Nina	122,404.88		Nina	73,627.20		Nina	119,417.28
29							Patrick	122,404.88		Patrick	182,389.64		Patrick	187,058.72		Patrick	413,695.78
30	Paul	190,563.85		Paul	122,404.88		Paul	123,383.68					Paul	224,755.13		Paul	134,911.44
31							Peter	160,939.74
32	Rebecca	224,755.13		Rebecca	278,186.04		Rebecca	119,162.93					Rebecca	224,755.13		Rebecca	250,733.76
33																Reuben	13,750.00
34																Rodney	449,510.26
35																Rodrick	353,208.88
36	Sade	449,510.26		Sade	674,265.39		Sade	1,581,405.02		Sade	55,220.40		Sade	122,404.88
37	Sharon	30,678.00		Sharon	461,845.66								Sharon	27,500.00
38	Stacy	892,977.14		Stacy	1,114,701.56		Stacy	1,530,023.22		Stacy	482,819.22		Stacy	224,755.13		Stacy	1,123,775.65
39										Stephen	1,581,405.02
40							Susan	167,987.23		Susan	192,500.00
41	Valerie	1,355,694.91		Valerie	130,893.53		Valerie	278,186.04		Valerie	208,639.53		Valerie	139,093.02		Valerie	682,384.50

[Randy Martins]

OK Shg

Let me give it a shot will get back to you shortly.

Thanks

[Randy Martins]

Hi Shg

Firstly let me say that the finished product that you are showing is exactly what I am looking for but I am still failing to get there. When I type in Alignkeys in the Macro name box it won't let me run. It only has the option to create see screen shot

Screenshot1.PNG

And then when I press create it goes to this screenshot

Capture 2.PNG

What am I doing wrong?

Also when I do the first paste in the name box do I paste exactly as you have put it 'Sort List'!keys or do I leave out the quotation marks like Sort List!keys?

[shg]

When I type in Alignkeys in the Macro name box it won't let me run.

Did you enable macros when you opened the AlignKeys workbook?

Try selecting the second macro in the list and pressing Run.

do I paste exactly as you have put it 'Sort List'!keys

Exactly as shown. The space in the sheet name makes the single quotes necessary.

[Randy Martins]

No I did not enable macros when I opened the alignkeys workbook. How do I do that?

[Randy Martins]

WoW Abracadabra

Shg It Worked. Thank You So much you da Man, this is going to be a Massive help. This is 100% percent solved now

[shg]

You're welcome.