Splitting up Data in Columns into Their Own Rows

**Tamarissa** · 07-26-2023, 06:08 PM

I am having a hard time figuring out how to go about getting all the information from a data download from one system into the correct layout I need to upload to another system.

I get all the data for a transaction on one line, but I need it split out into multiple lines for each type in certain columns.

Any suggestions on where to take this from here would be great; I've tried pivot tables, which I'm normally pretty savvy at, but no luck there. Not sure what other options exist after that. File attached shows the original data export and how I need to look after the magic.

Thanks so much!

Demo.xlsx

(column order of the output does not matter)

**AliGW** · 07-27-2023, 02:29 AM

Which version of Excel? Still Excel 2010?

**Tamarissa** · 07-31-2023, 11:56 AM

Microsoft 365

**Glenn Kennedy** · 07-31-2023, 12:06 PM

1. Please update your profile to reflect that.

2. Do you have two accounts here? I have seen this EXACT attachment here in the last week or two. I ABSOLUTELY remember working at it, but can not remember if I reached a conclusion.

3. If not, is this perhaps an assignment for a course that you have been given?

**Glenn Kennedy** · 07-31-2023, 12:08 PM

Noticing the date of your ORIGINAL post... I suspect that I saw your original post and gave up as it was too complicated for my tiny brain and for Excel 2010!!

**Tamarissa** · 07-31-2023, 03:37 PM

I do not have 2 accounts; this is the only thread I have created for this request. I started it last week. It is not for a course.

I will figure out how to change the profile... didn't know that was a thing.

My tiny brain can't figure it out either!

**JeteMc** · 08-01-2023, 12:19 AM

This proposal employs various formulas.
For the Date2:Cost, Project Manager:Unit # and CMS Job # columns:

Formula:

Please Login or Register  to view this content.

For the RT3:TT4 columns:

Formula:

Please Login or Register  to view this content.

For the LOA-Full column:

Formula:

Please Login or Register  to view this content.

For the Truck column:

Formula:

Please Login or Register  to view this content.

For the Misc Expense column:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

**Tamarissa** · 08-01-2023, 02:35 PM

I added the formulas from the demo file you attached, and they seem to work great up until the last 12 rows for some reason. Then it stops working correctly. I've attached a second file where you can see what happens and when the difference starts (row 797). In order to get it small enough to attach, I had to remove most of the formulas, so they have been removed in rows 7:775 & 821:4346 (end). I left them in the area just before, during and after the formula breaks goes wrong.

The original demo file was part of a much larger file (obviously), and I generally have over 1000 lines in this part. So it's also a little hard to tell how many rows I need to have these formulas added to, but I can probably figure out a way around that. But if you have any suggestions on that part as well, it would be appreciated.

As I also add data to this file every couple of days, so I had to add another field here to keep track of what I've entered already and what's new, so you will see that on the new file as well.

Demo 2.xlsx

**HansDouwe** · 08-01-2023, 05:07 PM

Please delete all expected results and try a single cell formula for the whole table in BH7:

Formula:

Please Login or Register  to view this content.

**Glenn Kennedy** · 08-02-2023, 04:56 AM

Hans, LoL. Your solution is beautifully simple! I was tying myself in knots trying to take parallel diagonal slices through a cumbersome 2D array that I'd created. I do not intend to share it here. It was simply horrible. And it didn't work.

**HansDouwe** · 08-02-2023, 05:11 AM

Thx, just like LEGO, playing and stacking with blocks.

**Tamarissa** · 08-02-2023, 12:59 PM

Thanks Hans, that worked nicely

**HansDouwe** · 08-02-2023, 02:09 PM

You are Welcome!

Thanks for your feedback, Glad to have helped.

If you haven't already, please consider adding reputation to all helpers you think they deserve.
In that case please click * Add Reputation left below their answers.

Splitting up Data in Columns into Their Own Rows

LinkBack

Thread Tools

Rate This Thread

Display

Splitting up Data in Columns into Their Own Rows

Re: Splitting up Data in Columns into Their Own Rows

Re: Splitting up Data in Columns into Their Own Rows

Re: Splitting up Data in Columns into Their Own Rows

Re: Splitting up Data in Columns into Their Own Rows

Re: Splitting up Data in Columns into Their Own Rows

Re: Splitting up Data in Columns into Their Own Rows

Re: Splitting up Data in Columns into Their Own Rows

Re: Splitting up Data in Columns into Their Own Rows

Re: Splitting up Data in Columns into Their Own Rows

Re: Splitting up Data in Columns into Their Own Rows

Re: Splitting up Data in Columns into Their Own Rows

Re: Splitting up Data in Columns into Their Own Rows

Thread Information

Users Browsing this Thread

Similar Threads

splitting one cell into multiple rows (and retaining all other data from other columns)

[SOLVED] Splitting 2000 rows of data into multiple columns

Splitting rows with multiple columns into seperate rows for time points.

Splitting words and numbers into separate columns and rows

Splitting Information in columns into seperate rows

Splitting cells, rearranging columns and rows, cleaning up data

[SOLVED] Splitting rows into columns...

Bookmarks

Bookmarks

Posting Permissions