Solved

Microsoft Data Manipulation Tools

Posted on 2014-04-03
17
214 Views
Last Modified: 2014-04-10
Is there a good little tool or toolset that provides additional function with regard to data manipulation and matching?  I have a worksheet that comes in each week.  I've added several additional columns and as soon as a refresh comes out, my worksheet no longer works....the data and number of rows have changed.  So I need something that matches the old sheet to the new sheet and bring in new data.

Any ideas or thoughts on tool sets.

B.
0
Comment
Question by:Bright01
  • 9
  • 8
17 Comments
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39976664
It shouldn't be too difficult to build a macro in VBA to handle the issue.

Please upload a sample.
0
 

Author Comment

by:Bright01
ID: 39977915
MicroShadow,

That's a bold statement!  OK..... so I mocked up the actual example but in very simple terms.  For this reason, as you take a look at this, please note there are many more columns and rows then appear in this sample.

Here's what I'm looking for;  I have a WS "OldData" and a new WS "NewData", I will need to have the ability to input the Source (i.e. OldData) and the Target (i.e. NewData) since the tabs will have different names periodically.  The macro needs to take the old data, and combine it with the new data to map into my personal WS (which is named "MyData".  You will see the changes to the table in new data in red and they are shown mapped into MyData also in red.

Hope this gives you a sense of what I'm trying to do.

Much thanks in advance,

B.
DATA-MAPPING.xlsm
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39978152
Ok.

Basically you have to loop both worksheets one row at a time comparing them, if not equal - loop the cells, if all cells in a row don't match - copy row from new worksheet to row beneath the current row in old worksheet, if some cells do match replace the ones that don't with the new data. Next copy the data in the newly updated old data worksheet to your personal worksheet.

This is the way I would do it on your sample. If you can confirm that the structure of the real worksheets is identical (regardless of number of columns and rows) I will start the coding.
0
 

Author Comment

by:Bright01
ID: 39978341
Yes.  The loop will identify a completely new row (a new record); while if there is an old row with new data in a specific cell, it will keep the old row but place the new cell data into the new WS.  Finally, and we may have missed this point, if a particular entire row has been deleted in the new data.... (i.e., it's in the old data but not in the new data WS, then remove all row data except the first row... so I will see that a row has been deleted in the new data).

Next....., the data from the New WS has to be mapped (you say copied) into my WS.

So my worksheet always has the newest data along with the data or formulas that are in the cells in my worksheet.

Finally, the part about identifying the Source and Target Worksheets?  That could be as simple as having two fields on my worksheet that lists the WS names.

Makes sense?

B.
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39978402
Yes it makes sense. I'm clocking out for the next 30 hours or so, I'll be back after that.
0
 

Author Comment

by:Bright01
ID: 39978470
ok...no worries...thank you!
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39980328
Initially I thought that the new data worksheet only includes the new or changed records. But you say that it may have deleted records, which indicates that the new data worksheet include all the records you need. If that is the case, then there is no need to compare the new data with the old data, just to "map" the new data to your personal worksheet.
0
 

Author Comment

by:Bright01
ID: 39980399
Think of it this way, each row as the data is compared from the Old to the New, has 1 of 3 conditions.  

1.) NO CHANGE; no data in the cells in that row have changed.  So it maps directly and completely into my data sheet.  

2.) CERTAIN CELLS IN THE ROW; Some or certain cells in that row have changed so that it now maps the new/changed cells, for that row into my data. And

3.) COMPLETE ROW IS DELETED OR REMOVED;  the entire row has been removed in the new file, so I'd like to be able to see when a complete row has been taken out (new vs. old WS).  

I misspoke in my previous response.  In order for me to know that the row has been deleted, I need to have some way of knowing that a complete row has been deleted from the new sheet (a row that was in the old sheet.)....some sort of flag.  I had originally thought that if the row had been entirely deleted, you may elect to leave the data in the first Column, thus flagging it for having been deleted.

I hope that is more clear.

Thank you for getting this right before putting something in place.

B.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 26

Expert Comment

by:MacroShadow
ID: 39980418
Just to make sure I understand, the only reason to compare the new data to the old data is to find records that were removed. All other records can be mapped into your data sheet, even if there are no changes, it will be a lot quicker to copy it all than to loop looking for differences.
0
 

Author Comment

by:Bright01
ID: 39980446
Yes...I believe you are correct.  All I would ask is to not hard code the Mapping Table.  It needs to be flexible.

Thank you,

B
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39980449
What kind of flexibility?
0
 

Author Comment

by:Bright01
ID: 39980586
So the reason I asked initially, if there were any tools available is because I'd like to use this over and over again.  Additionally, the New WS's I receive may have new Columns or My Data Sheet may change with regard to the position of a column (add, delete, move).  So what I had envisioned is having some form of mapping table (on another sheet) that would allow me to designate that Col. A in WS New Data is mapped to Col. C in WS My Data.  Why? Because in My Data, I had added Col. A and B and the I need the Data in Col. A in the New Data WS "Mapped" into Col. C in My Data.   This gives me flexibility to change the location of the Source and Target Columns.

Make sense?

B.
0
 

Author Comment

by:Bright01
ID: 39981916
Hope I didn't just scare you off ;-)

B.
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39982121
No, I'm trying to figure it out. It seems pretty complicated to detect deleted rows.
0
 

Author Comment

by:Bright01
ID: 39982137
I'd recommend we take this in steps  (finish the first and I'll author the next step in an additional question).  Create the Mapping capability first.  From one WS to the other by way of Column ID. and Source/Target WS.  To keep it simple, you would only need to compare one KEY Column and if it was different then the one in the Target, it would have to add it as an additional row.  Then I would need to manually figure out if it were an addition or simply a deletion from the original (Source).  Wouldn't that work?

B.
0
 
LVL 26

Accepted Solution

by:
MacroShadow earned 500 total points
ID: 39990015
Here is the mapping functionality:
To set it up:
Change the following to fit your situation
        Set Sh1 = .Sheets("Sheet1")    'Modify as necessary.
        Set Sh2 = .Sheets("Sheet2")    'Modify as necessary.

Open in new window

and
    HeadersOne() = Split("applicationname,applicationid,number", ",") ' headers of source sheet
    HeadersTwo() = Split("appname,appid,num", ",")                    ' headers of destination sheet

Open in new window


Now run the macro.

Sub Map()

    Dim Sh1 As Worksheet, Sh2 As Worksheet
    Dim HeaderIter As Long, SCol As Long, TCol As Long, LRow As Long, Iter As Long
    Dim HeadersOne() As String
    Dim HeadersTwo() As String

    With ThisWorkbook
        Set Sh1 = .Sheets("Sheet1")    'Modify as necessary.
        Set Sh2 = .Sheets("Sheet2")    'Modify as necessary.
    End With

    HeadersOne() = Split("applicationname,applicationid,number", ",") ' headers of source sheet
    HeadersTwo() = Split("appname,appid,num", ",")                    ' headers of destination sheet

    For HeaderIter = 1 To 3
        SCol = GetColMatched(Sh1, HeadersOne(HeaderIter - 1))
        TCol = GetColMatched(Sh2, HeadersTwo(HeaderIter - 1))
        LRow = GetLastRowMatched(Sh1, HeadersOne(HeaderIter - 1))

        For Iter = 2 To LRow
            Sh2.Cells(Iter, TCol).Value = Sh1.Cells(Iter, SCol).Value
        Next Iter
    Next HeaderIter

End Sub

Function GetLastRowMatched(Sh As Worksheet, Header As String) As Long
    Dim ColIndex As Long
    ColIndex = Application.Match(Header, Sh.Rows(1), 0)
    GetLastRowMatched = Sh.Cells(Rows.Count, ColIndex).End(xlUp).Row
End Function

Function GetColMatched(Sh As Worksheet, Header As String) As Long
    Dim ColIndex As Long
    ColIndex = Application.Match(Header, Sh.Rows(1), 0)
    GetColMatched = ColIndex
End Function

Open in new window


Check out this link it will provide you with an option to set up an interface.
0
 

Author Closing Comment

by:Bright01
ID: 39991181
Macroshadow,

Thank you so much for your requirements determination process and code!  I must say, you are not going to believe this.... based on your asking the right questions for narrowing down the appropriate macro, I began my own set of explorations for a tool that would not necessarily require coding.  I found it.  I'm going to use the ETL capability in SPSS.  I had someone demo it for me last night and it literally does the mapping I require.  So although your macro works for what I need, with SPSS I already have the interface.  I would not have looked around at SPSS if you hadn't asked all the right questions.   You deserve an "A".  Much thanks,

B.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now