• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 391
  • Last Modified:

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.
0
CipherIS
Asked:
CipherIS
  • 4
  • 3
2 Solutions
 
ScriptAddictCommented:
If it was me working on this, I would create two helper columns in spreadsheet two.

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.
0
 
CipherISAuthor Commented:
I need to automate the process which means coding in C# or writing a macro from excel that can be called by C#
0
 
tdlewisCommented:
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
CipherISAuthor Commented:
There is no macro attached to the spreadsheet.  

Also, any idea how to call it from C#?
0
 
tdlewisCommented:
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.
0
 
tdlewisCommented:
Try this version.
Q-28002863.xlsm
0
 
tdlewisCommented:
For instructions on calling your VBA macro from C#, see this article:
http://msdn.microsoft.com/en-us/library/vstudio/bb608613.aspx
0
 
CipherISAuthor Commented:
Thanks
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now