C# - MS Excel - Effectively Normalize Data in Spreadsheet
I need to normalize data in spreadsheet. The spread sheet contains 2 worksheets.
Worksheet1 - Contains persons First Name and Last Name - plus other identifying info.
Worksheet2 - Contains persons First Name and Last Name plus Spouse Name, Relationship, and Vehicles
The issue is Worksheet 2. The worksheet would be easier to work if it was in the below format.
John Doe Jane Doe 2010 Camaro SS
John Doe Jane Doe 2013 Acura TL
John Doe Jane Doe 2005 Ford Explorer
But the spreadsheet provided is using merged cells in Worksheet2 so the data looks like below
John Doe Jane Doe 2010 Camaro SS
2013 Acura TL
2005 Ford Explorer
I need to combine worksheet1 and worksheet2 into a text file linking them by name (John Doe - as his name would be on the first sheet).
What is the best way to do this? XML? List? etc...
Please provide code snippet if possible.
Worksheet1 - Contains persons First Name and Last Name - plus other identifying info.
Worksheet2 - Contains persons First Name and Last Name plus Spouse Name, Relationship, and Vehicles
The issue is Worksheet 2. The worksheet would be easier to work if it was in the below format.
John Doe Jane Doe 2010 Camaro SS
John Doe Jane Doe 2013 Acura TL
John Doe Jane Doe 2005 Ford Explorer
But the spreadsheet provided is using merged cells in Worksheet2 so the data looks like below
John Doe Jane Doe 2010 Camaro SS
2013 Acura TL
2005 Ford Explorer
I need to combine worksheet1 and worksheet2 into a text file linking them by name (John Doe - as his name would be on the first sheet).
What is the best way to do this? XML? List? etc...
Please provide code snippet if possible.
ASKER
I need to automate the process which means coding in C# or writing a macro from excel that can be called by C#
I think the macro in the attached worksheet comes pretty close to doing what you want. See the output that it generated in Sheet3.
Q-28002863.xlsm
Q-28002863.xlsm
ASKER
There is no macro attached to the spreadsheet.
Also, any idea how to call it from C#?
Also, any idea how to call it from C#?
I have no idea what happened to the macro that I wrote for this solution. I cannot find it anywhere on my computer. Hopefully I can recreate it without a lot of effort.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
in it I would put formula's that column something like =if(trim(a2)="",D1,A2). And then key off of the helper column.
I know you are working in C#, but you can still either build that into your code or update the spreadsheet with the two new columns before normalizing. And of course delete them afterwards.
The formula would be in Column D.