I have big data tables coming from old word documents that I'm trying to switch to a usable form in excel. The first column of the old tables contains "names" associated with multiple rows of other columns, the number of which can vary. It's easier to explain visually.

The structure I have looks like this:

Starting data

And I want to obtain something like this (without vba):

Desired result

I've tried everything and been stuck on this for days. The big problem is the random variable number of data and blank rows associated with each "name" from the first column.

Any help would be greatly appreciated!

  • 2
    "I've tried everything..." What is it that you have tried, exactly? – cybernetic.nomad Dec 9 '19 at 16:28

Sorting out this kind of messy data and restructuring or reordering it happens a lot. This is a general strategy that can be adapted to re-sorting data and other challenges. Unless we are talking much larger datasets my strategy is to:

(1) think of my output as a one- or two-D table. (2) work out the row and column location in the table corresponding to each row of data (3) use index/match formulas to populate the table accordingly

Probably best to give an example. Below we have some similarly structured made-up source data (Source Name & Ref).

In the next columns ("Increments") we keep track of the 'most recent' row and column number for each row of data. But to keep track of these when there are blank rows we have to carry over the previous row & column position (without any increments).

So the row number is incremented whenever (in this specific case) the first column for that row is "Name Of". Otherwise the row number remains the same.

Similarly the column number is incremented every row, except that when it is a blank row rather than a data row then the column number is not incremented. When the row number changes the column count is reset to 1.

All good, excep that there are now duplicate row and column numbers in the blank rows, so we create another pair of columns that are identical except that we blank out the duplicates ("Locations"). You simply can't do this in one step as you need the duplicates to carry the data down through all the rows including the blank ones.

enter image description here enter image description here

So at this point we know what the row and column location is for each line of data. Now we have to build the table.

We do this using index/match formulas, matching on a text string we create that uniquely identifies the location. For example, for row 2 column 3 we create a string "2-3". (See column labeled "Index")

So for row 2 column 3 of our table we know we are trying to match with a row of data with the index string of "2-3". Use MATCH() to find its row number and then INDEX() to pull out the data you want from that row.

When there is no string to match - for example for high column numbers once all the data for that row is in - an error is triggered. We can catch this with the IFERROR() function.

enter image description here

enter image description here

Not sure how clear this is ... happy to explain more... this is quite a useful general technique.

  • 1
    [QUESTION RESOLVED] This is exactly what I was looking for! Thanks Mr. Bouvier! For the ones looking for an answer: this "increments" method works admirably well and is easily adaptable to various different cases to restructure data. His post gives a general method to restructure data rather than just giving ready-made formula's for a specific problem, so it's really worth putting quite some time in this. I'm pretty sure I'm going to use this everywhere from now on. – Qosa Dec 11 '19 at 10:21

One way to handle the problem:

Make every line be "complete" with the name repeated as many times as required. i.e. for every line that it is connected to... remove the "name"-only lines entirely.

This is actually quite common, and the "only" easily handled format of data. e.g. in using "Autofilter"

Autofilter: Select all the data including the header row and hold/hit CTRL-SHIFT+L to turn it on, or off.
Menu:Data>Autofilter is the same in LibreOffice, hmm; don't know the menu item for Excel

  • Thanks, but I found a solution with Bouvier's answer :). – Qosa Dec 11 '19 at 10:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.