vlookup
 

I have a list of data with multiple values being the same in column A with
different lists of data in columns B, C, and D. I need to consolidate the
data in column A to one record or row and have the data in columns B, C, and
D move to single row. Any suggestions?

If they're all numbers, I would try a Pivot Table.
HTH,
Gary Brown

"PJG" wrote:

I have a list of data with multiple values being the same in column A with
different lists of data in columns B, C, and D. I need to consolidate the
data in column A to one record or row and have the data in columns B, C, and
D move to single row. Any suggestions?

1. Tools | Option | General | R1C1 reference style
2. Tools | Option | Calculation | Iteration on
3. Enter this formula into a cell
=IF(AND(RC1=R[1]C1,RC1=R[-1]C1),IF(R[-1]C=0,R[1]C,R[-1]C),
IF(RC1=R[1]C1,R[1]C,IF(RC1=R[-1]C1,R[-1]C,"")))
4. Edit | Copy this cell
5. Select data area
6. Edit | Goto | Special | Blanks | OK
7. Edit | Paste
8. Select data area
9. Edit | Copy
10. Edit | Paste Special | Value | OK

Hi Herbert,

I studied PJG's question. I understood it as:
- make from
a 1 2 3
b 4 5
a 6 7
c 8 9
- this
a 1 2 3 6 7
b 4 5
c 8 9
I could not think of a simple solution. Of cource, anything is possible in
VBA but
that was to much for me.
Then I read you approach. Could you give a (short) explanation? It looks
interesting
but why RC-notation, why iteration? Last but not least, does it work?

Kind regards, Franss

"Herbert Seidenberg" wrote in message
oups.com...
1. Tools | Option | General | R1C1 reference style
2. Tools | Option | Calculation | Iteration on
3. Enter this formula into a cell
=IF(AND(RC1=R[1]C1,RC1=R[-1]C1),IF(R[-1]C=0,R[1]C,R[-1]C),
IF(RC1=R[1]C1,R[1]C,IF(RC1=R[-1]C1,R[-1]C,"")))
4. Edit | Copy this cell
5. Select data area
6. Edit | Goto | Special | Blanks | OK
7. Edit | Paste
8. Select data area
9. Edit | Copy
10. Edit | Paste Special | Value | OK

Hi Frans
Like your array, I visualized an array with repeats in the first column
and random entries or blanks in assigned columns:
Who Tag_a Tag_b Tag_c Tag_d
Art aa
Art ab
Art ad
Bud ba
Bud bc
Bud bb
Cid cb
Cid cc cd
Dan dc
Dan db
Dan da dd

The result should be:
Who Tag_a Tag_b Tag_c Tag_d
Art aa ab ad
Bud ba bb bc
Cid cb cc cd
Dan da db dc dd

My approach was to fill all the blanks in a tag column belonging to the
same person with the data appearing somewhere in that tag column, and
then deleting duplicate rows.
The formula refers to column locations only one cell away, so iteration
is needed to fill the rest of the cells. Five iterations are needed in
this example. Think of it as automata calculations.
R1C1 notation maintains the wording of the formula independent of its
location and makes it more readable.
Solutions to erasing duplicate rows have been provided by gurus
previously.