Excel row to column Transpose question

[Squeaks]

I have a dataset in this format

Person1, value1
Person1, value2
Person2, value3
Person2, value4
Person2, value5

but need it to look like this

Person1, value1, value2
Person2, value3, value4, value5

Because the values are variable a normal crosstab/pivot does not work and because the the person is variable a usual copy/paste transpose does not seem to work

I guess I need a little code/macro but that is beyond my abilities. Has anyone a solution to this?

Any help greatly appreciated
Les

 

[ETID]

Assigning a the headers P and V for Person and Value
I ran a pivot on your example and got this...


Count of V V
P value1 value2 value3 value4 value5 Grand Total
Person1 1 1 2
Person2 1 1 1 3
Grand Total 1 1 1 1 1 5


Does this help?

 

[Zathras]

Assuming your data are in columns A and B starting in cell A1, here is a non-VBA solution for you:

1. Put these formulae in the cells indicated:[blue]

     C1: =B1
     C2: =IF(A2=A1,C1&", "&B2,B2)
     D1: =IF(A1=A2,1,0)

[/color]
2. Copy the formula from C2 down the column.
3. Copy the formula from D1 down the column.
4. Copy columns C and D and PasteSpecial.../Values
5. Sort the worksheet on column D (ascending)
6. Delete the rows that have a "1" in column D
7. Delete column B

 

[ETID]

Ooops...looks more like this...

Count of V V
P value1 value2 value3 value4 value5 Grand Total
Person1 1 1 2
Person2 1 1 1 3
Grand Total 1 1 1 1 1 5

 

[ETID]

Dang!!..the format doesn't work,...Oh well It looks well organized in excel

 

[Squeaks]

Thanks chaps

the if statements worked a treat Zathras, why do we (*I*) always look for a more complex answer!!

Hi ETID, the crosstab unfortunately does not work because the real data would have hundreds of actual values and permutations (too many column headings), sorry I never mentioned that LOL..many thanks to you both for taking the time to resolve this for me.

regards
Les

 

[DSMan]

Zathras' general approach should work, but you'll need to modify it to take account of situations where you have more than two occurences of the same person. (Your example had three for the second person, but there may be more down in the middle somewhere). Also, it sounded as if you wanted the data in different cells

Similar approach to Zathras', but first thing to do is to do a pivot table with person as a row field and count of person as the data. Then do a quick scan (or even better, use the max function) to find out the maximum number of rows for one person that you can have. (For the sake of example, let's say it's 15).

Then as in Zathras' suggestion
In Col C
C1 : = A1
C2: =if(A2=A1,"",A2) (this will give the person's name if it is the first
occurence, otherwise it will be a null string)
Copy this down

In column d
D1: = if(c1="","",B1) copied down

In Columns E to R
E1 = if(c1="", "" ,if(A2=A1,"",B2)) copied down
F1 = if(c1="", "" ,if(A3=A1,"",B3)) copied down
accross to
R1 = if(c1="", "" , if(A15=A1,"",B15)) copied down

This will give sixteen columns C to R, where only the first row of each person has any
data, column C has the name and columns D to R have the separate entries up to the maximum of 15, as below

A B C D E F G H
Person1 Value1 Person1 Value1 Value2
Person1 Value2
Person2 Value3 Person2 Value3 Value4 Value5
Person2 Value4
Person2 Value5


then, as in Zithras' example do a copy and paste values (preferably to a different worksheet!) sort by column C and delete the unwanted rows and cols A and B

 

[johnwtek]

Quick tip I just learned. To make this table readable, you can monospace it using [ignore][tt] or

[/ignore], like this:

[ignore][tt]
P         value1 value2 value3 value4 value5    Grand Total
Person1        1    1                               2
Person2                   1      1       1          3
Grand Total    1    1     1      1       1          5
[/tt][/ignore]

Then it looks like this in your posting:
[tt]
P         value1 value2 value3 value4 value5    Grand Total
Person1        1    1                               2
Person2                   1      1       1          3
Grand Total    1    1     1      1       1          5
[/tt]

 

[Squeaks]

Thank you all

The spreadsheet is setup and running with live data, thankfully with no more than 7 values per person

regards