Automating a concatenate scenario...

[Waxaholic]

I have columned data that i would like to concatenate by row. I would like to automate this via an excel formula if possible. The data in question looks like this:

1 0 300 A
1 1 275 A
1 2 250 D
2 0 326 A
2 1 301 D
3 0 323 A
3 1 302 D
3 2 281 D
3 3 260 D

I need to move the data in Column A and Column C into row format (concatenate). I also need the data to be listed on seperate rows according to coulmn D. The result would hopefully look like this:

1 300 275
1 250
2 326
2 301
3 323
3 302 281 260

etc..

Can someone give me some insight as to where to start on this? Much appreciated.

Wax

 

[Waxaholic]

Ooooops. I meant to say Transpose instead of concatenate.

Sorry for the confusion.

Wax

 

[JVFriederick]

Do you still need help on this ?
I think an equation can be written to do it.

 

[Waxaholic]

Yes I do. I mistakingly referred to the task at hand as concatenate but really meant transpose. I would appreciate any help you might have.

Waxaholic

 

[JVFriederick]

I split the task into groups of A and group D, that go into separate tables that can be combined later.
Layout a grid like this starting on row 13, which has blanks in columns A and B.

A B C D E F


0 1 2 3
1 A 300 275 0 0
2 A 326 0 0 0
3 A 323 0 0 0

In cell C14, which shows 300, enter the following equation:
=SUM(($D$1:$D$9=$B14)*($A$1:$A$9=$A14)*($B$1:$B$9=C$13)*($C$1:$C$9))

Briefly, look in Data column D and match with Table value B14, look in Data column A and match with Table value A14, look in Data column B and match with Table value C13, look in Data column B and match with Table value C13, then sum column C.

Press Crtl-Enter so the equation now will be surrounded by {}. This assumes you data range is A19 (as you posted earlier). Copy the equation in C14 to C14:F16.

The next table begin at A20. The equations from table A will copy just fine.

1 2 3 4
1 D 0 250 0 0
2 D 301 0 0 0
3 D 302 281 260 0

Sorry about being so brief - see if you can make it work - I can help more later.........

 

[Waxaholic]

I am a little confused here. The formula fails on the first equation ($D$1:$D$9=$B14). This refers to Column D which contains text (A or D) and cannot be Summed. I set up the grid and sample data as above but it returns #VALUE!. I thought it might be easier to split the data prior to creating the equation. Hence, i split the data into an "A" and "D" data sets. Now "A" and "D" do not need to be included in the equation. The only references need to be column A and column B with column C being the source of the returned value. I tried it this way also and it failed again. Any idea where i am going wrong with this?

Waxaholic

 

[JVFriederick]

Send an Email to JVFriederick@Yahoo.com and I'll send an example file back.