CODEWITHSUNDEEP
excelexcel-formulavlookup
scorgn
scorgn

Reputation: 3619

How to combine multiple columns which may or may not contain the same information in spreadsheet?

Okay, so I don't exactly know how to word this so I will show what I want to do.

| A     |   | B     |   | C     |   |
| Bob   | 1 | John  | 4 | Bob   | 7 |
| John  | 3 | Sally | 2 | John  | 3 |
| Sally | 7 |       |   | Sally | 3 |
| Chris | 3 |       |   | Hana  | 9 |
|       |   |       |   | Ruth  | 6 |

And I want to combine them all by the names, making it like this...

|       | A | B | C |
| Bob   | 1 | 4 | 7 |
| John  | 3 |   | 3 |
| Sally | 7 | 2 | 3 |
| Hana  |   |   | 9 |
| Ruth  |   |   | 6 |
| Chris | 3 |   |   |

What would be the easiest way to do this?

I have access to OpenOffice Calc, Microsoft Excel and Google Sheets.

Upvotes: 1

Views: 82

Answers (2)

EBH
EBH

Reputation: 10440

You can use a Pivot-Table, after vectorizing your table. The advantage in this approach is that the final result is automatically update in any change (after you press "refresh" on the Pivot-Table). Here is how you do it:

  1. and another column after each value column, with the A,B and C repeating all the way to the end of the table. This could be done very quickly, and I guess it won't be a problem.

  2. Define all the range of the table as a named range (data hereafter), and use the following formula to vectorize it all:

    =OFFSET(data,TRUNC((ROW()-ROW($K$2))/COLUMNS(data)),MOD(ROW()-ROW($K$2),COLUMNS(data)),1,1)

    while $K$2 is the first cell of the vector (where you paste the formula), and you just drug it all the way down until you reach the end of the data:

    combining columns

    in this example column K has all the values from the table.

  3. With three simple formulas break down the vector to three columns (and give the column som meaningful names):

    in column L above: =IF(AND(ISTEXT($K2),LEN($K2)>1),$K2,"")
    in column M above: =IF(AND(ISTEXT($K2),LEN($K2)>1),$K4,"")
    in column N above: =IF(AND(ISTEXT($K2),LEN($K2)>1),$K3,"")

    I assume here that you can distinguish by a rule between what is a Name and what is equivalent to A,B,C. If the logical rules above not fit your specific situation, change to match your data.

  4. Create a Pivot-Table based upon this columns to cross tabulate them as you want:

    Pivot

If you need more explanation on any of the steps, feel free to comment me ;)

Upvotes: 0

Eileen R
Eileen R

Reputation: 136

Since you don't want to cut and paste, I assume this is either:

a) a large volume of data, or b) something you'll have to do often,

or both. Knowing which will help me.

In most cases, you would first create a single name list, by copying to another location each column below the other, then using Remove Duplicates.

2) Create two tables that look like this: pic of tables

with these formulas copied down:

I2:  =VLOOKUP(H2,$A$2:$B$7,2,FALSE)
J2:  =VLOOKUP(H2,$C$2:$D$7,2,FALSE)
K2:  =VLOOKUP(H2,$E$2:$F$7,2,FALSE)

Upvotes: 2

Related Questions