So Hopefully I have describe this properly.
I have some (lots, 19,000 rows) data that exports from a system we use at work. Unfortunately, rather than just dumping into a nice pretty table, it has a bunch of sub-headings occupying single cells as you move through the data. For example, Column A might look like this:
Subheading1 Data1.1 Data1.2 Data1.3 Dta1.4 Data1.5 Subheading2 Data2.1 Data2.2 Data2.3 Data2.5 Data2.5
etc etc.
What I need to do is manipulate this data so that it can easily drop into a pivot table to summarize a few different ways, but this (and some other) annoying formatting is causing issues. What I would like is a formula or macro that would make the data set look like this:
Subheading Description Subheading1 Data1.1 Subheading1 Data1.2 Subheading1 Data1.3 Subheading1 Data1.4 Subheading1 Data1.5 Subheading2 Data2.1 Subheading2 Data2.2 Subheading2 Data2.3 Subheading2 Data2.4 Subheading2 Data2.5
So basically I create a new column so the initial data is now in column B and in Column A, it drags out the subheading from Column B, deletes the line that the subheading was on, and fills the subheading down to all data points (rows) that were under the subheading, and does this until it encounters the next subheading at which point it stops filling down subheading1 and repeats the process for subheading2.
Basically there are a bunch of useless dummy rows (and other stuff I haven't tackled yet) in the data set and I need to pull them out but apply the subheading label to an adjacent column so that I can still sort by that categorization in a pivot table.
Bookmarks