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
– James, Commented Dec 4, 2013 at 22:11

Community · Accepted Answer · 2017-05-23 11:56:09Z

0

(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.

CommunityBot

11 silver badge

answered Dec 4, 2013 at 23:03

user20416

15.5k9 gold badges76 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bf2020 · Accepted Answer · 2013-12-04 23:38:55Z

0

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.

answered Dec 4, 2013 at 23:38

bf2020

7324 silver badges7 bronze badges