Combining duplications and data
 

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

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

Combining duplications and data
 

Max:

This worked.

Thank you.

Bill


"Max" wrote:

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

Combining duplications and data
 

welcome, delighted that it worked for you
--
Max
Singapore
"Bill" wrote in message
...
Max:
This worked
Thank you
Bill

Combining duplications and data
 

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 .

Thanks for the help Max.

Bill

"Max" wrote:

welcome, delighted that it worked for you
--
Max
Singapore
"Bill" wrote in message
...
Max:
This worked
Thank you
Bill


.

Combining duplications and data
 

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





"Bill" wrote:

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 .

Thanks for the help Max.

Bill

"Max" wrote:

welcome, delighted that it worked for you
--
Max
Singapore
"Bill" wrote in message
...
Max:
This worked
Thank you
Bill


.

Combining duplications and data
 

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

With your source data in A2:D2 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
Singapore
---
"Bill" wrote:
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

Combining duplications and data
 

Max:

It worked.

Thanks again.

Bill

"Max" wrote:

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

With your source data in A2:D2 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
Singapore
---
"Bill" wrote:
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

Combining duplications and data
 

welcome, Bill
--
Max
Singapore

"Bill" wrote in message
...

Max:
It worked.
Thanks again
Bill