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

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

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

welcome, delighted that it worked for you
--
Max
Singapore
"Bill" wrote in message
...
Max:
This worked
Thank 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 .

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


.

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


.

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

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

welcome, Bill
--
Max
Singapore

"Bill" wrote in message
...

Max:
It worked.
Thanks again
Bill