arrays - Iterative formulas in Excel to reformat data onto a new sheet without using VBA

Question

I'm quite stuck with this project and I'm hoping that perhaps I'm either on the right track or in the right place to ask as I'm very much so a novice when it comes to Excel and using functions within it.

Currently I've been given a data set that's formatted in a very unfortunate way for my needs. In the photo I've created the most simplistic sample of similar data I could, though the actual data set I'm working with is typically about 144 rows by 35 columns. In case the photo doesn't show, the best way I can describe this data is:

• column A will have agent names that each take up 3 vertical merged cells
• column B will have "average credit","Credit amount" and "credit volume", each taking up one cell and repeating down the column until reaching the last of the merged cells for the agent names
• And beginning in row 1 cols c through h will have the dates starting from 6/1/2021 through 6/6/2021

Then C2:H16 are filled out with corresponding numbers for the credit each agent applied each day. If the agent didn't apply a credit that day, the corresponding 3 cells are left blank.

What I would like to do is, on a separate sheet in the same workbook, use some kind of formula or something that can get my data to be a simple table with the date, agent name, average credit, credit amount, and credit volume as my headers, as pictured below:

Ideally I'd even like to only place values in the new sheet should there be a valid, non blank cell under the date but realistically I'm perfectly fine with any type of nested formula that could get the job done whether it filters those out or not. The unfortunate thing is this has to be done without using VBA and without using the built-in ribbon options in Excel.

Initially I hoped I could achieve this with some sort of nested vlookup and if statement but I'm a bit over my head. What I have planned as a last ditch effort is to create 31 additional sheets within the workbook and on each sheet use the filter function while filtering for a different column on each sheet, ie sheet 2 would filter for non blank data points under column c, sheet 3 would filter non blank data points under column d etc, and though it would be tedious I would then just copy and transpose the data into a completely separate workbook.

Any help, hints, tips or tricks are very greatly appreciated.

Answer 1

I see two methods:

1. Power Query - must have Excel for Windows 2010 or higher.
2. Formula - must have Excel 2019 or 365.

Power Query (PQ) Method

This may look involved, but it takes less than 5 minutes and will be done completely by mouse. Once done, the data can be updated by simply clicking refresh.

First convert the table into an excel table and import into the PQ editor by going to the Data tab and selecting From Table/Range:

Say yes to table having headers:

This will open the PQ editor. Select the agent name field and from the Transform tab, select Fill Down.

Now select the agent name and column1 and then right-click and choose Unpivot Other Columns.

Now your data are almost there - they are normalized into columns with attributes,

so you need to pivot them into the form that you proposed above. Next step is to select Column1 and then Pivot from the Transform tab. It will bring up a window where you can select Value as the field that you want to pivot:

Click OK and now you can see the table that you wanted:

Now you need to get this back into Excel. On the Home tab, click Close and Load to:

and then choose Table and New worksheet:

And you're done.

If you get new data added to your data, you can just right-click the table you created above and select Refresh and it will update the table with the new data. If you want to apply it to future tables of data, it is easily modified without having to repeat the whole process.

Formula Method

This is already a long post and this method requires some serious formula engineering, so I will only highlight this method for now, but if it is interesting, say so in the comments and I will complete the post.

This is a formula that unpivots, but it is not suited to your table because:

• it needs those merged names to be populated into the cells below (i.e. fill-down) and
• it treats blank cells as zeros.

In Power Query parlance, we would say that you want to "Unpivot C1:H16 By A1:B16".

=LET( unPivMatrix, C1:H16,
      byMatrix, A1:B16,
       upC, COLUMNS( unPivMatrix ),
       byC, COLUMNS( byMatrix ),
       dmxR, MIN( ROWS( unPivMatrix ), ROWS( byMatrix ) ) - 1,
       upCells, dmxR * upC,
       upSeq, SEQUENCE( upCells,, 0 ),
       upHdr, INDEX( INDEX( unPivMatrix, 1, ),  1,  SEQUENCE( upC ) ),
       upBody, INDEX( unPivMatrix,  SEQUENCE( dmxR ) + 1,  SEQUENCE( 1, upC ) ),
       byBody, INDEX( byMatrix,  SEQUENCE( dmxR ) + 1,  SEQUENCE( 1, byC ) ),
       attr, INDEX( upHdr, MOD( upSeq, upC ) + 1 ),
       mux, INDEX( upBody, upSeq/upC + 1, MOD( upSeq, upC ) + 1 ),
       demux, IFERROR( INDEX(
                             IFERROR( INDEX( byBody,
                                             IFERROR( INT( SEQUENCE( upCells, byC,0 )/byC/upC ) + 1, MOD( upSeq, upC ) + 1 ),
                                                      SEQUENCE( 1, byC + 1 ) ),
                                       attr ),
                             upSeq + 1, SEQUENCE( 1, byC + 2 ) ),
                        mux ),
       demux
     )

To modify this would require an equivalent of Fill-Down, which is a little challenging, but possible. It would also require a filter at the end to remove those zeros.

This would not deliver your final output, it would only give you the first step. You would need to then pivot the unpivoted output which could be done by a normal excel pivot table or by writing yet another complex formula to pivot it, which is hard.

So... if you have Excel for Windows 2010 or higher, I would go for Power Query. It was designed for problems just like yours.