Create a single row for duplicate rows based on two columns (Not One)

[CramitCCCAs]

Attached data has an identifier in column A, Column C has the plan description and column B has the matching percentage that goes with the plan. I need separate columns for the "Bonus Plan" (Column C) but the cell values need to be populated with the data in "Bonus Plan Assignment" (Column B)

EE Data.xlsx
	A	B	C
1	Employee ID	Bonus Plan Assignment	Bonus Plan
2	1002685	10% Annual: Individual Target	Individual Bonus Objectives (IBO)
3	1002692	49% Annual: Individual Target	Individual Bonus Objectives (IBO)
4	1002703	14% Annual: Individual Target	Individual Bonus Objectives (IBO)
5	1002706	12% Annual: Individual Target	Individual Bonus Objectives (IBO)
6	1002717	14% Annual: Individual Target	Individual Bonus Objectives (IBO)
7	1002719	14% Annual: Individual Target	Individual Bonus Objectives (IBO)
8	507050	11.76% Annual: Individual Target	Individual Bonus Objectives (IBO)
9	1002270	10% Annual: Individual Target	Individual Bonus Objectives (IBO)
10	507042	10% Annual: Individual Target	Individual Bonus Objectives (IBO)
11	1002550	10% Annual: Individual Target	Individual Bonus Objectives (IBO)
12	507314	13% Annual: Individual Target	Individual Bonus Objectives (IBO)
13	507724	5% Annual: Individual Target	Individual Bonus Objectives (IBO)
14	507724	5% Quarterly: Individual Target	Corporate Earnings (CE)
15	1001759	7% Annual: Individual Target	Individual Bonus Objectives (IBO)
16	1002736	25% Annual: Individual Target	Individual Bonus Objectives (IBO)
17	1005206	2.22% Annual: Individual Target	Individual Bonus Objectives (IBO)
18	1005206	2.24% Quarterly: Individual Target	Resuscitation Earnings (RE)
19	005960	9% Annual: Individual Target	Individual Bonus Objectives (IBO)
20	1001599	12.5% Quarterly: Individual Target	Business Achievement (BA) Public Safety
21	1001599	5% Annual: Individual Target	Individual Bonus Objectives (IBO)
22	1001599	7.5% Quarterly: Individual Target	Resuscitation Earnings (RE)
23	507188	10% Annual: Individual Target	Individual Bonus Objectives (IBO)
24	507188	5% Quarterly: Individual Target	Resuscitation Earnings (RE)
25	507823	3.75% Annual: Individual Target	Individual Bonus Objectives (IBO)
26	507823	3.75% Quarterly: Individual Target	Business Achievement (BA) EMS
27	507823	3.75% Quarterly: Individual Target	Business Achievement (BA) - Hospital
28	507823	3.75% Quarterly: Individual Target	Resuscitation Earnings (RE)
29	507316	11% Quarterly: Individual Target	Resuscitation Earnings (RE)
30	507316	17% Annual: Individual Target	Individual Bonus Objectives (IBO)
31	1002671	28% Annual: Individual Target	Individual Bonus Objectives (IBO)
32	1004631	5% Annual: Individual Target	Individual Bonus Objectives (IBO)
33	507277	5% Annual: Individual Target	Individual Bonus Objectives (IBO)
34	507277	5% Quarterly: Individual Target	Resuscitation Earnings (RE)
35	507268	9% Annual: Individual Target	Individual Bonus Objectives (IBO)
36	1003101	24% Annual: Individual Target	Individual Bonus Objectives (IBO)
37	507211	10% Annual: Individual Target	Individual Bonus Objectives (IBO)
38	507211	5% Quarterly: Individual Target	Resuscitation Earnings (RE)
39	1002864	7.93% Annual: Individual Target	Individual Bonus Objectives (IBO)
40	1004853	10% Annual: Individual Target	Individual Bonus Objectives (IBO)
41	1002665	49% Annual: Individual Target	Individual Bonus Objectives (IBO)
42	1003776	5% Quarterly: Individual Target	Resuscitation Earnings (RE)
43	005458	8% Annual: Individual Target	Individual Bonus Objectives (IBO)
44	005458	8% Quarterly: Individual Target	Resuscitation Earnings (RE)
45	005733	10% Quarterly: Individual Target	Resuscitation Earnings (RE)
46	005733	20% Annual: Individual Target	Individual Bonus Objectives (IBO)
47	507514	12% Annual: Individual Target	Individual Bonus Objectives (IBO)
48	1002788	5.22% Annual: Individual Target	Individual Bonus Objectives (IBO)
49	507732	8% Annual: Individual Target	Individual Bonus Objectives (IBO)
50	507637	10% Annual: Individual Target	Individual Bonus Objectives (IBO)
51	507637	5% Quarterly: Individual Target	Resuscitation Earnings (RE)
52	507824	5% Annual: Individual Target	Individual Bonus Objectives (IBO)
53	507824	5% Quarterly: Individual Target	Resuscitation Earnings (RE)
54	1005150	12% Annual: Individual Target	Individual Bonus Objectives (IBO)
55	507274	10% Annual: Individual Target	Individual Bonus Objectives (IBO)
56	005682	12% Annual: Individual Target	Individual Bonus Objectives (IBO)
57	1002784	7.53% Annual: Individual Target	Individual Bonus Objectives (IBO)
58	1002861	8.61% Annual: Individual Target	Individual Bonus Objectives (IBO)
59	1002861	8.61% Quarterly: Individual Target	Resuscitation Earnings (RE)
60	1002861	8.61% Quarterly: Individual Target	Business Achievement (BA) - Hospital
61	507387	19.3% Annual: Individual Target	Individual Bonus Objectives (IBO)
62	507387	7.7% Quarterly: Individual Target	Resuscitation Earnings (RE)
63	1002824	5.45% Annual: Individual Target	Individual Bonus Objectives (IBO)
64	1003016	3.2% Annual: Individual Target	Individual Bonus Objectives (IBO)
65	1003016	4.79% Quarterly: Individual Target	Resuscitation Earnings (RE)
66	507854	5% Annual: Individual Target	Individual Bonus Objectives (IBO)
67	507854	5% Quarterly: Individual Target	Resuscitation Earnings (RE)
68	1002013	10% Annual: Individual Target	Individual Bonus Objectives (IBO)
69	1002013	10% Quarterly: Individual Target	Corporate Earnings (CE)
70	006119	13% Annual: Individual Target	Individual Bonus Objectives (IBO)
71	1002980	30.37% Annual: Individual Target	Individual Bonus Objectives (IBO)
72	1002980	38.65% Quarterly: Individual Target	Resuscitation Earnings (RE)
73	507405	9% Annual: Individual Target	Individual Bonus Objectives (IBO)
74	005900	4% Quarterly: Individual Target	Resuscitation Earnings (RE)
75	005900	8% Annual: Individual Target	Individual Bonus Objectives (IBO)
76	507737	6% Annual: Individual Target	Individual Bonus Objectives (IBO)
77	1001227	6% Annual: Individual Target	Individual Bonus Objectives (IBO)
78	1001227	6% Quarterly: Individual Target	Resuscitation Earnings (RE)
79	1000018	10% Annual: Individual Target	Individual Bonus Objectives (IBO)
80	1005078	5% Annual: Individual Target	Individual Bonus Objectives (IBO)
81	1005078	5% Quarterly: Individual Target	Corporate Earnings (CE)
82	1002100	7% Annual: Individual Target	Individual Bonus Objectives (IBO)
83	1002981	2.72% Quarterly: Individual Target	Resuscitation Earnings (RE)
84	1002981	4.07% Annual: Individual Target	Individual Bonus Objectives (IBO)
85	507544	8% Annual: Individual Target	Individual Bonus Objectives (IBO)
86	1002932	2.64% Annual: Individual Target	Individual Bonus Objectives (IBO)
87	1002932	3.88% Quarterly: Individual Target	Resuscitation Earnings (RE)
88	1002940	5.25% Annual: Individual Target	Individual Bonus Objectives (IBO)
89	1002940	7.88% Quarterly: Individual Target	Resuscitation Earnings (RE)
90	507238	7% Annual: Individual Target	Individual Bonus Objectives (IBO)
91	005978	5% Annual: Individual Target	Individual Bonus Objectives (IBO)
92	005978	5% Quarterly: Individual Target	Corporate Earnings (CE)
93	1002982	3% Annual: Individual Target	Individual Bonus Objectives (IBO)
94	1002982	4.55% Quarterly: Individual Target	Resuscitation Earnings (RE)
95	006090	17% Annual: Individual Target	Individual Bonus Objectives (IBO)
96	1004991	10.53% Annual: Individual Target	Individual Bonus Objectives (IBO)
97	005840	7% Annual: Individual Target	Individual Bonus Objectives (IBO)
98	107554	8% Annual: Individual Target	Individual Bonus Objectives (IBO)
99	507100	10% Annual: Individual Target	Individual Bonus Objectives (IBO)
100	507100	5% Quarterly: Individual Target	Resuscitation Earnings (RE)
101	005939	10% Annual: Individual Target	Individual Bonus Objectives (IBO)
102	005939	5% Quarterly: Individual Target	Resuscitation Earnings (RE)
103	1002809	3.23% Annual: Individual Target	Individual Bonus Objectives (IBO)
104	1002809	5.1% Quarterly: Individual Target	Resuscitation Earnings (RE)
105	1004242	5% Annual: Individual Target	Individual Bonus Objectives (IBO)
106	1004854	7% Annual: Individual Target	Individual Bonus Objectives (IBO)
107	200146	2.5% Quarterly: Individual Target	Business Achievement (BA) - Hospital
108	200146	7.5% Annual: Individual Target	Individual Bonus Objectives (IBO)
109	1004728	10% Annual: Individual Target	Individual Bonus Objectives (IBO)
110	107538	4% Annual: Individual Target	Individual Bonus Objectives (IBO)
111	107538	4% Quarterly: Individual Target	Resuscitation Earnings (RE)
112	005951	9% Annual: Individual Target	Individual Bonus Objectives (IBO)
113	1004910	13.43% Annual: Individual Target	Individual Bonus Objectives (IBO)
114	1004910	8.96% Quarterly: Individual Target	Resuscitation Earnings (RE)
115	1002114	6% Annual: Individual Target	Individual Bonus Objectives (IBO)
116	1003094	3.72% Annual: Individual Target	Individual Bonus Objectives (IBO)
117	1003094	5.59% Quarterly: Individual Target	Resuscitation Earnings (RE)
118	006188	14% Annual: Individual Target	Individual Bonus Objectives (IBO)
119	006188	6% Quarterly: Individual Target	Resuscitation Earnings (RE)
120	507451	5% Annual: Individual Target	Individual Bonus Objectives (IBO)
121	507451	5% Quarterly: Individual Target	Resuscitation Earnings (RE)
122	1004855	9% Annual: Individual Target	Individual Bonus Objectives (IBO)
123	005387	10% Quarterly: Individual Target	Resuscitation Earnings (RE)
124	005387	20% Annual: Individual Target	Individual Bonus Objectives (IBO)
125	005062	8.32% Annual: Individual Target	Individual Bonus Objectives (IBO)
126	005655	17% Annual: Individual Target	Individual Bonus Objectives (IBO)
127	507499	7% Annual: Individual Target	Individual Bonus Objectives (IBO)
128	1002739	24% Annual: Individual Target	Individual Bonus Objectives (IBO)
129	507568	9% Annual: Individual Target	Individual Bonus Objectives (IBO)
130	1001336	12% Annual: Individual Target	Individual Bonus Objectives (IBO)
131	1002632	7% Annual: Individual Target	Individual Bonus Objectives (IBO)
132	1002785	10.12% Annual: Individual Target	Individual Bonus Objectives (IBO)
133	507599	7% Annual: Individual Target	Individual Bonus Objectives (IBO)
134	1004699	5% Annual: Individual Target	Individual Bonus Objectives (IBO)
135	1004699	5% Quarterly: Individual Target	Resuscitation Earnings (RE)
136	507249	12% Annual: Individual Target	Individual Bonus Objectives (IBO)
137	507489	5% Annual: Individual Target	Individual Bonus Objectives (IBO)
138	507552	3.62% Quarterly: Individual Target	Resuscitation Earnings (RE)
139	507552	9.05% Annual: Individual Target	Individual Bonus Objectives (IBO)
140	507552	9.05% Quarterly: Individual Target	Business Achievement (BA) Public Safety
141	507260	10% Annual: Individual Target	Individual Bonus Objectives (IBO)
142	1005113	4.53% Annual: Individual Target	Individual Bonus Objectives (IBO)
143	1005113	6.8% Quarterly: Individual Target	Resuscitation Earnings (RE)
144	005584	10% Annual: Individual Target	Individual Bonus Objectives (IBO)
145	005584	14% Quarterly: Individual Target	Resuscitation Earnings (RE)
146	507406	8% Annual: Individual Target	Individual Bonus Objectives (IBO)
147	507495	12% Annual: Individual Target	Individual Bonus Objectives (IBO)
148	1001650	10% Annual: Individual Target	Individual Bonus Objectives (IBO)
149	507763	15% Annual: Individual Target	Individual Bonus Objectives (IBO)
150	507763	5% Quarterly: Individual Target	Resuscitation Earnings (RE)
151	1004744	4% Annual: Individual Target	Individual Bonus Objectives (IBO)
152	1004744	4% Quarterly: Individual Target	Corporate Earnings (CE)
153	507570	10% Annual: Individual Target	Individual Bonus Objectives (IBO)
154	005278	7% Annual: Individual Target	Individual Bonus Objectives (IBO)
155	005362	7% Annual: Individual Target	Individual Bonus Objectives (IBO)
156	507629	9% Annual: Individual Target	Individual Bonus Objectives (IBO)
157	1002754	28% Annual: Individual Target	Individual Bonus Objectives (IBO)
158	1004856	7% Annual: Individual Target	Individual Bonus Objectives (IBO)
159	507190	10% Annual: Individual Target	Individual Bonus Objectives (IBO)
160	507190	5% Quarterly: Individual Target	Resuscitation Earnings (RE)
161	1002781	7% Annual: Individual Target	Individual Bonus Objectives (IBO)
162	507146	9% Annual: Individual Target	Individual Bonus Objectives (IBO)
163	1002634	7% Annual: Individual Target	Individual Bonus Objectives (IBO)
164	1002751	28% Annual: Individual Target	Individual Bonus Objectives (IBO)
165	1002755	10.5% Annual: Individual Target	Individual Bonus Objectives (IBO)
166	1002726	14% Annual: Individual Target	Individual Bonus Objectives (IBO)
167	1002746	24% Annual: Individual Target	Individual Bonus Objectives (IBO)
168	1002683	32% Annual: Individual Target	Individual Bonus Objectives (IBO)
169	1005130	5% Annual: Individual Target	Individual Bonus Objectives (IBO)
170	1005130	5% Quarterly: Individual Target	Corporate Earnings (CE)
171	001578	5% Annual: Individual Target	Individual Bonus Objectives (IBO)
172	507135	8.33% Annual: Individual Target	Individual Bonus Objectives (IBO)
173	107448	10% Annual: Individual Target	Individual Bonus Objectives (IBO)
174	1005324	10% Annual: Individual Target	Individual Bonus Objectives (IBO)
175	1003000	4.86% Annual: Individual Target	Individual Bonus Objectives (IBO)
176	1003000	7.19% Quarterly: Individual Target	Resuscitation Earnings (RE)
177	507693	5% Annual: Individual Target	Individual Bonus Objectives (IBO)
178	507693	5% Quarterly: Individual Target	Resuscitation Earnings (RE)
179	1002943	3.33% Quarterly: Individual Target	Resuscitation Earnings (RE)
180	1002943	6.2% Annual: Individual Target	Individual Bonus Objectives (IBO)
181	1005160	2.5% Quarterly: Individual Target	Business Achievement (BA) Public Safety
182	1005160	7.5% Annual: Individual Target	Individual Bonus Objectives (IBO)
183	005705	11% Annual: Individual Target	Individual Bonus Objectives (IBO)
184	005705	4% Quarterly: Individual Target	Resuscitation Earnings (RE)
185	1001402	5% Annual: Individual Target	Individual Bonus Objectives (IBO)
186	801838	4% Quarterly: Individual Target	Resuscitation Earnings (RE)
187	801838	6% Annual: Individual Target	Individual Bonus Objectives (IBO)
188	507380	4% Quarterly: Individual Target	Resuscitation Earnings (RE)
189	507380	8% Annual: Individual Target	Individual Bonus Objectives (IBO)
190	1002317	10% Annual: Individual Target	Individual Bonus Objectives (IBO)
191	1002317	5% Quarterly: Individual Target	Resuscitation Earnings (RE)
192	1004857	9% Annual: Individual Target	Individual Bonus Objectives (IBO)
193	1002905	11.75% Quarterly: Individual Target	Resuscitation Earnings (RE)
194	1002905	2.94% Annual: Individual Target	Individual Bonus Objectives (IBO)
195	1002866	3.94% Annual: Individual Target	Individual Bonus Objectives (IBO)
196	1002866	7.33% Quarterly: Individual Target	Resuscitation Earnings (RE)
197	1004960	1.25% Quarterly: Individual Target	Business Achievement (BA) - Hospital
198	1004960	1.25% Quarterly: Individual Target	Business Achievement (BA) EMS
199	1004960	7.5% Annual: Individual Target	Individual Bonus Objectives (IBO)
200	507178	5% Annual: Individual Target	Individual Bonus Objectives (IBO)
201	507178	5% Quarterly: Individual Target	Corporate Earnings (CE)
EE Data

 

[mrshl9898]

Like this?

Or with the Bonus plan as headers in new columns?

 

[CramitCCCAs]

Sorry, no. I wasn't as clear as I needed to be. One column for each separate value in in Column C (6 different plans) populated with the corresponding value Column B.

 

[mrshl9898]

Like this then?

Sub Macro2()

Dim rownum As Long
Dim rownum2 As Long
Dim colnum As Long
Dim success As Long
Dim Bonus As String
Dim BonusPlan As String

    Range(Range("C1"), Range("C1").End(xlDown)).Copy Range("F1")
    Range(Range("F1"), Range("F1").End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlYes
    Range(Range("F1"), Range("F1").End(xlDown)).Copy
    Range("G1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Columns("F:F").Delete Shift:=xlToLeft
   
rownum = 2
Do Until Cells(rownum, 1) = ""
Bonus = Cells(rownum, 3)
BonusPlan = Cells(rownum, 2)
colnum = 7
    Do Until Cells(1, colnum) = Bonus
        colnum = colnum + 1
    Loop
        rownum2 = 2
        success = 0
            Do Until success = 1
                If Cells(rownum2, colnum) = BonusPlan Then
                success = 1
                ElseIf Cells(rownum2, colnum) = "" Then
                Cells(rownum2, colnum) = BonusPlan
                success = 1
                End If
            rownum2 = rownum2 + 1
            Loop
rownum = rownum + 1
Loop
           
End Sub

*examples are always helpful

 

[CramitCCCAs]

I tried the code, but it errored looking for an end sub? Sorry about not providing an example I'll be sure to include one next time!

 

[mrshl9898]

Edited above, apologies. Bad copy and paste

 

[CramitCCCAs]

After adding the End Sub it did create the columns and added the values but the identifiers were ignored, so its not really what I was looking for. Ideally I need a single value for Column A where there are duplicates, then a column each for the corresponding values in Column C populated with the values in Column B. Sorry for my confusion and lack of detail. The rows in Yellow would be deleted.

EE Data2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Employee ID	Bonus Plan Assignment	Bonus Plan	Individual Bonus Objectives (IBO)	Corporate Earnings (CE)	Resuscitation Earnings (RE)	Business Achievement (BA) Public Safety	Business Achievement (BA) EMS	Business Achievement (BA) - Hospital	Business Achievement (BA) Ventilation	Business Achievement (BA) Military	Business Achievement (BA)
2	1002685	10% Annual: Individual Target	Individual Bonus Objectives (IBO)	10% Annual: Individual Target
3	1002692	49% Annual: Individual Target	Individual Bonus Objectives (IBO)	49% Annual: Individual Target
4	1002703	14% Annual: Individual Target	Individual Bonus Objectives (IBO)	14% Annual: Individual Target
5	1002706	12% Annual: Individual Target	Individual Bonus Objectives (IBO)	12% Annual: Individual Target
6	1002717	14% Annual: Individual Target	Individual Bonus Objectives (IBO)	14% Annual: Individual Target
7	1002719	14% Annual: Individual Target	Individual Bonus Objectives (IBO)	14% Annual: Individual Target
8	507050	11.76% Annual: Individual Target	Individual Bonus Objectives (IBO)	11.76% Annual: Individual Target
9	1002270	10% Annual: Individual Target	Individual Bonus Objectives (IBO)	10% Annual: Individual Target
10	507042	10% Annual: Individual Target	Individual Bonus Objectives (IBO)	10% Annual: Individual Target
11	1002550	10% Annual: Individual Target	Individual Bonus Objectives (IBO)	10% Annual: Individual Target
12	507314	13% Annual: Individual Target	Individual Bonus Objectives (IBO)	13% Annual: Individual Target
13	507724	5% Annual: Individual Target	Individual Bonus Objectives (IBO)	5% Annual: Individual Target	5% Quarterly: Individual Target
14	507724	5% Quarterly: Individual Target	Corporate Earnings (CE)
15	1001759	7% Annual: Individual Target	Individual Bonus Objectives (IBO)	7% Annual: Individual Target
16	1002736	25% Annual: Individual Target	Individual Bonus Objectives (IBO)	25% Annual: Individual Target
17	1005206	2.22% Annual: Individual Target	Individual Bonus Objectives (IBO)	2.22% Annual: Individual Target		2.24% Quarterly: Individual Target
18	1005206	2.24% Quarterly: Individual Target	Resuscitation Earnings (RE)
19	005960	9% Annual: Individual Target	Individual Bonus Objectives (IBO)	Individual Bonus Objectives (IBO)
20	1001599	12.5% Quarterly: Individual Target	Business Achievement (BA) Public Safety	5% Annual: Individual Target		7.5% Quarterly: Individual Target	12.5% Quarterly: Individual Target
21	1001599	5% Annual: Individual Target	Individual Bonus Objectives (IBO)
22	1001599	7.5% Quarterly: Individual Target	Resuscitation Earnings (RE)
EE Data

 

[mrshl9898]

So remove duplicates in A, then the rest should work?

Sub Macro2()

Dim rownum As Long
Dim rownum2 As Long
Dim colnum As Long
Dim success As Long
Dim Bonus As String
Dim BonusPlan As String

    Range(Range("A1"), Range("C1").End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlYes
    Range(Range("C1"), Range("C1").End(xlDown)).Copy Range("F1")
    Range(Range("F1"), Range("F1").End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlYes
    Range(Range("F1"), Range("F1").End(xlDown)).Copy
    Range("G1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Columns("F:F").Delete Shift:=xlToLeft
   
rownum = 2
Do Until Cells(rownum, 1) = ""
Bonus = Cells(rownum, 3)
BonusPlan = Cells(rownum, 2)
colnum = 7
    Do Until Cells(1, colnum) = Bonus
        colnum = colnum + 1
    Loop
        rownum2 = 2
        success = 0
            Do Until success = 1
                If Cells(rownum2, colnum) = BonusPlan Then
                success = 1
                ElseIf Cells(rownum2, colnum) = "" Then
                Cells(rownum2, colnum) = BonusPlan
                success = 1
                End If
            rownum2 = rownum2 + 1
            Loop
rownum = rownum + 1
Loop

Columns("D:F").Delete Shift:=xlToLeft

End Sub

 

[CramitCCCAs]

Thanks again, however I haven't put us on the same page. The duplicates can't just be deleted, they correspond to the values needed in each row. I'm not trying to parse, I'm trying to lineup a single value for Column A where the values for "B" & "C" are in a single row but match, rather then being listed multiple times in column A

 

[CramitCCCAs]

Can anyone help? I'm looking to dedupe column A and create a single unique record (row) for each value in column A, while populating 6 columns created by the values in Column C, with the values in column B, all while corresponding to the original IDs in column A.