QUESTION :
I’ve exported delivery addresses from Pastel into an Excel Spreadsheet. Each address record that Pastel spat out has a lot of data for each customer that I don’t need.
Given the screenshot here, I just need the values on B2, B3, D4, and D5 for each account. I can see there’s 7 rows splitting these values for each account. What I need to do is collate these values for capture in CSV format, but I can’t work out how to do it.
On Sheet2, I’ve been trying something like this and copying it, but it’s incrementing by 1 row, I need it to increment by 7, can anyone help me to figure out how to do this?
=Sheet1!B2
ANSWER :
Based on the layout of your worksheet, the following formulas will pick out the cells you are looking for.
Cell F1: =INDEX($B:$D,ROW($F1)*7-5,1)
Cell G2: =INDEX($B:$D,ROW($F1)*7-4,1)
Cell H2: =INDEX($B:$D,ROW($F1)*7-3,3)
Cell I2: =INDEX($B:$D,ROW($F1)*7-2,3)
After entering the first set, just copy them down the number of rows needed to capture all the data (which should be the total number of data rows divided by 7).
The INDEX function has the following syntax: INDEX(lookup_range, row number, column number). For example, INDEX($B:$D,1,2) would return the contents of the cell in the first row and second column in $B:$D, which is cell C1.
When the formulas are copied down, ROW($F1) returns the numbers 1,2,3…, which are then multiplied by 7 to do the skip you need. That product needs to be adjusted to get the right row within the 7-row data groups. Hence the -5,-4, etc. in the formulas. The third argument just selects the correct column to return: 1 => column B and 3 => column D.
For the first one, use cells D2:G2 to capture the information that you want. Then turn filtering on (Sort & Filter -> Filter), and filter only the cells that have ‘Account’ in the first column. Then fill down D2:G2 to the bottom. You can copy-and-paste the filled cells to a contiguous range in a new workbook/worksheet.