Solved

C# - MS Excel - Effectively Normalize Data in Spreadsheet

Posted on 2013-01-21
8
364 Views
Last Modified: 2013-02-04
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
Comment
Question by:CipherIS
  • 4
  • 3
8 Comments
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 38802592
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
 
LVL 1

Author Comment

by:CipherIS
ID: 38803114
I need to automate the process which means coding in C# or writing a macro from excel that can be called by C#
0
 
LVL 10

Expert Comment

by:tdlewis
ID: 38804109
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 1

Author Comment

by:CipherIS
ID: 38805773
There is no macro attached to the spreadsheet.  

Also, any idea how to call it from C#?
0
 
LVL 10

Expert Comment

by:tdlewis
ID: 38807989
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
 
LVL 10

Assisted Solution

by:tdlewis
tdlewis earned 500 total points
ID: 38808100
Try this version.
Q-28002863.xlsm
0
 
LVL 10

Accepted Solution

by:
tdlewis earned 500 total points
ID: 38811132
For instructions on calling your VBA macro from C#, see this article:
http://msdn.microsoft.com/en-us/library/vstudio/bb608613.aspx
0
 
LVL 1

Author Closing Comment

by:CipherIS
ID: 38852733
Thanks
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question