Combining duplications and data

[Bill]

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

 

[Max]

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),"",INDEX(A:A,SMALL(D,ROWS($1:1))))
In F2, normal ENTER to confirm will do:
=IF(ISNA(MATCH(1,INDEX(($A$2:$A$100=$E2)*(B$2:B$100<>""),),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

 

[Bill]

Max:

This worked.

Thank you.

Bill

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),"",INDEX(A:A,SMALL(D,ROWS($1:1))))
In F2, normal ENTER to confirm will do:
=IF(ISNA(MATCH(1,INDEX(($A$2:$A$100=$E2)*(B$2:B$100<>""),),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]

welcome, delighted that it worked for you

 

[Bill]

Max:

I have one additonal aspect of that issue that I did not convey in the
original e-mail. It is a little difficult to show here because of limited
formating capability. If you are interested, I can send you a very short file
that would better capture what I am trying to accomplish. I would need your
e-mail. Mine is (e-mail address removed).

Thanks for the help Max.

Bill

 

[Bill]

Max:

On second thought, I might try and convey the issue in the example below.

Column A has colors
Column B has names
Column C has numbers
Column D also has numbers

A B C D

Red Jack 7
Red Jack 3
Blue Bill 1
Orange Bob 5
Red Steve 3
Blue Bill 7
Black Ron 8
Blue Ed 7
Orange Jack 4
Orange Jack 9

I would like to achieve the following combining the information (columns A,
C and D) for the same names in column B into a one line entry. I want to
also keep colors in column A; names in column B; numbers in column C; and
numbers in column D.

A B C D

Red Jack 7 3
Blue Bill 7 1
Orange Bob 5
Red Steve 3
Black Ron 8
Blue Ed 7
Orange Jack 4 9


Can this be done?

Thanks

Bill

 

[Max]

This extension of the earlier will consider cols A & B together for
uniqueness purposes

With your source data in A22 down to row 100
In E2:
=IF(OR(A2="",B2=""),"",IF(SUMPRODUCT((A$2:A2=A2)*(B$2:B2=B2))>1,"",ROW()))

In F2:
=IF(ROWS($1:1)>COUNT($E:$E),"",INDEX(A:A,SMALL($E:$E,ROWS($1:1))))
Copy to G2

In H2
=IF(ISNA(MATCH(1,INDEX(($A$2:$A$100=$F2)*($B$2:$B$100=$G2)*(C$2:C$100<>""),),0)),"",INDEX(C$2:C$100,MATCH(1,INDEX(($A$2:$A$100=$F2)*($B$2:$B$100=$G2)*(C$2:C$100<>""),),0)))
Copy to I2. Select E2:I2, copy down to I100. Success? celebrate it, hit Yes
below

 

[Bill]

Max:

It worked.

Thanks again.

Bill

This extension of the earlier will consider cols A & B together for
uniqueness purposes

With your source data in A22 down to row 100
In E2:
=IF(OR(A2="",B2=""),"",IF(SUMPRODUCT((A$2:A2=A2)*(B$2:B2=B2))>1,"",ROW()))

In F2:
=IF(ROWS($1:1)>COUNT($E:$E),"",INDEX(A:A,SMALL($E:$E,ROWS($1:1))))
Copy to G2

In H2:
=IF(ISNA(MATCH(1,INDEX(($A$2:$A$100=$F2)*($B$2:$B$100=$G2)*(C$2:C$100<>""),),0)),"",INDEX(C$2:C$100,MATCH(1,INDEX(($A$2:$A$100=$F2)*($B$2:$B$100=$G2)*(C$2:C$100<>""),),0)))
Copy to I2. Select E2:I2, copy down to I100. Success? celebrate it, hit Yes
below

 

[Max]

welcome, Bill