Excel "Normalize" data (Multiple Duplicative Columns to 3 Columns)

Question

I'm not quite sure what the terminology is here...

My data's columns contain a person ID, and 126 repeating columns of date1, amount1, date2, amount2, date3, amount3, etc.

There is no logic to the dates or amounts, except that date2 and amount2 will only contain data if date1 and amount1 have data, and so on.

I need three columns, personID, Date, and Amount.

So you are trying to merge the content the date/amount/ columns? Not sure there is a full question in your post. — James, Dec 04 '13 at 22:11

score 0 · Answer 1 · edited May 23 '17 at 11:56

(This might be overkill for a one-time scenario).

I would suggest you connect to the Excel file with ADO (see here). Then you can issue the following SQL statement against the Excel file:

SELECT PersonID, Date1, Amount1
FROM [WorksheetName$]

UNION ALL SELECT PersonID, Date2, Amount2
FROM [WorksheetName$]
WHERE Date2 IS NOT NULL

etc.

You probably want to generate the statement with some code, instead of writing it 60-odd times by hand.

Then, you can SELECT INTO a new worksheet. See here.

score 0 · Answer 2 · answered Dec 04 '13 at 23:38

Copy the whole file first thing, just to ensure a backup.

Then insert 126 columns before current column A

In new A1 write the formula =IF(DW1="",C1,DW1)

In new B2 write the formula =IF(DX1="",D1,DX1)

Copy the two cells and paste rightwards until DV1 (Ctrl-C then Ctrl-Shift-RightArrow then Ctrl-V)

Next copy A1:DV1 and paste downwards as far as needed. Copy columns A:B and paste into a new worksheet as values, et voila.

2 Answers2