Need help concatenating data across multiple columns every 4th row

[TkcRogue]

Hello,

This is my first time posting to this site and have come across a problem i can't get the answer to.

Here is my data:

[TABLE="width: 1404"]
<tbody>[TR]
[TD]Item ID[/TD]
[TD]Manufacturer[/TD]
[TD]Equipment Status[/TD]
[TD]Status[/TD]
[TD]Due Date[/TD]
[TD]Time[/TD]
[TD]Tracking[/TD]
[TD]Current Location[/TD]
[TD]Last Location[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]Toy Company[/TD]
[TD]New[/TD]
[TD]Ready to Ship[/TD]
[TD]25-Jan-2015[/TD]
[TD]12:00[/TD]
[TD]45685[/TD]
[TD]Dept: Assembly Location[/TD]
[TD]Dept: Unknown[/TD]
[/TR]
[TR]
[TD]Train[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5:00[/TD]
[TD][/TD]
[TD].... Shelf: 247[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1.2.3.9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Last Move Date : 30-Jan-2013[/TD]
[/TR]
[TR]
[TD]45678[/TD]
[TD]Unknown[/TD]
[TD]Recall[/TD]
[TD]Receive[/TD]
[TD]19-Apr-2015[/TD]
[TD][/TD]
[TD][/TD]
[TD]Dept: Loading Dock[/TD]
[TD]Dept: Shipping[/TD]
[/TR]
[TR]
[TD]Blanket[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Unknown[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5.6.9.8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Last Move Date : 27-Oct-2014[/TD]
[/TR]
[TR]
[TD]54678-9[/TD]
[TD]Two-Wheeler[/TD]
[TD]Refurbished[/TD]
[TD]Recall Sent[/TD]
[TD]17-Jul-2016[/TD]
[TD][/TD]
[TD][/TD]
[TD]Dept: Shipping[/TD]
[TD]Dept: Receiving[/TD]
[/TR]
[TR]
[TD]Bike[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD].... Shelf: 23[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]45.98.85[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Last Move Date : 24-Jul-2012[/TD]
[/TR]
</tbody>[/TABLE]

And this is what i need it to look like to the last row (that changes every week):

[TABLE="width: 1441"]
<tbody>[TR]
[TD]Item ID[/TD]
[TD]Manufacturer[/TD]
[TD]Equipment Status[/TD]
[TD]Status[/TD]
[TD]Due Date[/TD]
[TD]Time[/TD]
[TD]Tracking[/TD]
[TD]Current Location[/TD]
[TD]Last Location[/TD]
[/TR]
[TR]
[TD]12345 Train[/TD]
[TD]Toy Company[/TD]
[TD]New[/TD]
[TD]Ready to Ship[/TD]
[TD]25-Jan-2015[/TD]
[TD]0.5 0.208333333333333[/TD]
[TD]45685[/TD]
[TD]Dept: Assembly Location .... Shelf: 247[/TD]
[TD]Dept: Unknown Location 1.2.3.9 Last Move Date : 30-Jan-2013[/TD]
[/TR]
[TR]
[TD]45678 Blanket[/TD]
[TD]Unknown[/TD]
[TD]Recall[/TD]
[TD]Receive[/TD]
[TD]19-Apr-2015[/TD]
[TD][/TD]
[TD][/TD]
[TD]Dept: Loading Dock Unknown[/TD]
[TD]Dept: Shipping Location 5.6.9.8 Last Move Date : 27-Oct-2014[/TD]
[/TR]
[TR]
[TD]54678-9 Bike[/TD]
[TD]Two-Wheeler[/TD]
[TD]Refurbished[/TD]
[TD]Recall Sent[/TD]
[TD]17-Jul-2016[/TD]
[TD][/TD]
[TD][/TD]
[TD]Dept: Shipping .... Shelf: 23[/TD]
[TD]Dept: Receiving Location 45.98.85 Last Move Date : 24-Jul-2012[/TD]
[/TR]
</tbody>[/TABLE]

I would post code i have tried but i haven't gotten anything even close to working hope someone can help me out.

 

[joshman108]

Could you explain more clearly what it is you need to happen?

 

[edwin1682006]

what data you want to calculate or format? the top and bottom table looks similar

 

[TkcRogue]

My apologizes it browser didn't keep the lines in. The two tables have the same data but the top table is split into 4 lines ie. A2, A3, A4 A5. I need to keep the heading but concatenate the data for the rest of the table into one line every four lines so,

Dept: Receiving
Location
45.98.85
Last Move Date : 24-Jul-2012

I need it to from from this to this:
Dept: Receiving Location 45.98.85 Last Move Date : 24-Jul-2012

and i need it to do it every for lines - A2, A3, A4 A5 into A2 and A6, A7, A8, A9 into A3 and so on for the rest of the page and to go across to column L doing the same thing.

Let me know if this makes sense this is my first try explaining excel in words.

Thank you.

 

[edwin1682006]

in new cell J2 (next to last Location header) use =IF(ISBLANK(D2),"",CONCATENATE(I2,I3,I4,I5))
D2 is "Status" column, it will show blank if it is same line of data
You can then drag down the formula to the end. Do this for all columns.
At the end, you need to filter out the blank cell, then copy and paste the data into new spreadsheet