Combining duplications and data
Here's one pure formulas play to drive the entire results set out ..
Assume your source data as posted in A2:C2 down till say, row 100
In D2:
=IF(A2="","",IF(COUNTIF(A$2:A2,A2)1,"",ROW()))
In E2:
=IF(ROWS($1:1)COUNT(D:D),"",INDEX(A:A,SMALL(D:D,R OWS($1:1))))
In F2, normal ENTER to confirm will do:
=IF(ISNA(MATCH(1,INDEX(($A$2:$A$100=$E2)*(B$2:B$10 0<""),),0)),"",INDEX(B$2:B$100,MATCH(1,INDEX(($A$ 2:$A$100=$E2)*(B$2:B$100<""),),0)))
Copy F2 to G2. Select D2:G2, copy down to G100. Minimize/hide col D. Cols E
to G would return the desired results. voila? eternalize it, hit the YES
below
--
Max
Singapore
---
"Bill" wrote:
Using Excel 2007
I have information in columns A, B and C as shown below:
A B C
Bob 5
Steve 4
Bob X
John 7
John Y
Ron 8
I would like to combine similar entries in column A into one entry (one
line) in column A and with the corresponding values in columns B and C on one
line as shown below:
A B C
Bob 5 X
Steve 4
John 7 Y
Ron 8
I would like to do this in the simplest way possible and without using a
macro.
Any suggestions are appreciated.
Thank you,
Bill
|