0

I need to re-order data in a large excel file. Please suggest how can I get the required output.

Screenshot of excel file

1
  • I would think you would need either a VBA or Power Query solution. Apr 11, 2020 at 18:53

1 Answer 1

1

Various ways to do it come to mind. A traditional approach might be to use OFFSET() to allow you to calculate the portions of each address you need. But another way comes to mind:

=VLOOKUP(A$11,INDIRECT(CHAR(64+IF(COLUMN()<=3,1,3))&(ROW()-11)*3-1&":"&CHAR(64+IF(COLUMN()<=3,2,4))&(ROW()-11)*3-1+2),2,FALSE)

This formula is set up assuming the displayed material has "Old File" in row 1, and the first instance of Username in cell A2. It also requires that the column headers be identical to the labels next to each bit of data in the old file.

The basic idea is that you know the row with the headers for the new file and can hand enter it into the formula as the value to look up. Using the $ only for the row (which does not change) lets the column label change as you copy across in your output rows. This way, each cell's lookup will be done using its column's header. Yet only one formula is needed, not one that is edited for each column.

The table range is different for each row's lookup, but not the formulas, just the formula's result. So the same formula can be copied down as well as right without modification. Just the one formula is needed.

For the table reference, you have two kinds of items to calculate, a column and a row, and must find two of each. I used an IF() test for the letter portion. There are TWO of these in the formula as a range needs a starting cell and an ending cell. For the start of the range, I used one that gives 1 as a result for the first three columns, and 3 for the rest (this would need "more" if you really have more columns than shown in your example, but is relatively easy) so the start of the range used in the first three lookups will be column A and column C for the other three's start of range column. The character code for "A" is 65, so if you add the above to one less, 64, you will get 65 and 67 so "A" and "C". Similarly for the end of the range's column letter, but the IF() gives 2 or 4 for them resulting in "B" or "D".

For the row calculations, it is easier. For the start of the range's row number, you need the row you are in's number and must subtract 11 (the header row's number) from it (so you are saying I am one row down, two rows down, etc.) Since your data is in groups of three rows, you multiply that by three. If you had eight row groups, you'd multiply by eight and so on. Finally, subtract 1 for the "Old File" label's row. So, one row above, subtract 1. For 13 rows above your data, subtract 13, and so on.

For the end of the range's row number, it is the same calculation but adding 2 after subtracting the 1. Or combine values, but that will make maintaining the formula harder as time passes.

That is all wrapped in an INDIRECT() function to turn it into a real boy, er, real range.

Since each lookup range is two columns wide, you need not adjust the lookup column for the two different lookups. And use "FALSE" for the kind of lookup so you get exact results, not "close" results. The way that would happen would be if a label in the original data is incorrect so the lookup fails to find a perfect match. YOu would see an error which you can "count" outside the extraction and repair before fixing your data into something permanent. (Use a COUNTIF() in some cell, looking for errors. One hopes for 0, of course.) The bigger reason is that those old data headers are NOT sorted (Username coming before Book No. for instance, so 3 of your six lookups would give the wrong result if you used TRUE!

Often one does not have internal labelling like that and so must use the OFFSET() approach.

I believe your data is also in suitable form for one of the preparatory functions in setting up a Pivot Table to be of help. It takes data that is not in a single column of labels with a single column of data next to it and puts it so. If you did that, you could stop there, and not continue with the Pivot Table, and use TRANSPOSE() to turn sets of 6 rows into a horizontal one row, six columns, display. Using OFFSET() or INDIRECT() (like above) could let you keep them all together, or even easier, you could have a row of data, five rows of blanks, and repeat, then copy and paste your data as values, then sort to move it all together with no blank rows.

But that side-by-side thing makes me think the Pivot Table prep function won't work here so I have not researched it (I don't use Pivot Tables a lot).

But the formula above works, just the one, copied six columns wide by however many rows you need.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .