Search code examples
excelformattingdata-processing

How to cut and paste values from a row based on Blank column in Excel?

Hello and thanks for your help in advance.

What I am trying to do in Excel is cut and paste values based on if a column is blank.

Here is an example of what I have:

enter image description here

This is what my data looks like. It is an ID column and then several other descriptor columns. However the row which contains the column names repeats after every instance.

So, ARX_model2 is associated with all the columns starting with the blank column and its associated number until the last lettered column and its associated number.

After that, the next row on the ID column, such as ARX_model3 is associated with the next set of columns starting with the blank column and its associated number until the last lettered column.

What I aim to do here is to repeatedly cut/paste out every set of the associated numbers and end up with this:

enter image description here

I have to use the blank column as a reference because the number of descriptor columns, i.e. B, C, G, E, varies, but every new set always starts with a blank column.

Any help on how to do this in Excel would be greatly appreciated.

Solution

  • Well, this one was hard.

    I made something that you can adapt to your needs:

    enter image description here

    Your question was too difficult to understand, until I saw properly the image. Your data is in 1 single row, and you want to separate that row into smaller groups of data, each group in 1 row. The way you separate groups is those blank columns. And all groups have same size.

    Once I got that, I managed a formula to get the nth position of a blank inside a range, so for first model i want to get the first coincidence, for second model i want the second coincidence, and so on.

    That part of the formula is thanks to ExcelJet:

    How to Find Nth Occurrence in Excel

    I adapted the formula to work on columns, not rows, that's all I made:

    enter image description here

    So I've made up this array formula:

    =INDEX($A$2:$Y$2;1;SMALL(SI($A$1:$Y$1="";COLUMN($A$1:$Y$1)-COLUMN($A$1)+1);ROW()-ROW($B$12))+COLUMN()-COLUMN($B$12))

    Because it's an array formula, it must be entered pressing CTRL+ENTER+SHIFT or it won't work!

    This is how it works:

    1. SMALL(SI($A$1:$Y$1="";COLUMN($A$1:$Y$1)-COLUMN($A$1)+1);ROW()-ROW($B$12) will find the nth coincidence. The trick here is ROW()-ROW($B$12) because it controls the nth part when dragging down. So for first model the result will be 1, for second one will be 2, and so on. So this will return a number where it the nth blank header cell.
    2. The number from step 1 is used inside an INDEX function, to get a specific value always on first row, but different column. The part +COLUMN()-COLUMN($B$12) just controls the target column, when you drag to right the formula, so it's dynamic.

    NOTE: Please, notice that in cell A1 I typed the word MODELS. I had to do it, because if A1 is blank, then the formula won't work properly. Just type any text on it.

    I've uploaded a sample to Google Drive in case you want to check the formulas:

    https://drive.google.com/file/d/15joXnmw0fejoUg1SkaXCtNITXtQX2uvI/view?usp=sharing