Solved

Data Normalization

Posted on 2015-01-18
16
68 Views
Last Modified: 2015-01-26
In the attached sheet "PROBLEM" it has two tabs. "Data" & "Working"

The Data tab gets deleted and replaced almost daily. The working tab is the format to use to handle calculations. When the Data worksheet is imported I would need VBA code to loop through the data one row at a time and split the cell value from being X/X to two cells in the "Working" worksheet.
The primary key field is a unique value.

Cell Y4 on the data worksheet would need to be split into cells AH6 and AJ6 on the working worksheet. And so on through the cells, then the next row.

I have tried formulas but loose everything every time I delete and re-import a new Data worksheet.

Some help here would be appreciated.
PROBLEM.xlsx
0
Comment
Question by:DougDodge
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 6
16 Comments
 
LVL 30

Expert Comment

by:gowflow
ID: 40557363
Question:

1) Will the daily data plugged in sheet Data will always start at: Row 4 and the columns from Y to extreme right ?
2) Are columns variable or fixed like always Y to AG or can change ?
3) Will the result in working always plugged in Row 6 and down and Col AH to extreme right ? and also variable columns or fix ?

gowlfow
0
 
LVL 30

Expert Comment

by:gowflow
ID: 40557376
Also an other question

1) What about column C on sheet working how is it filled ? should the macro fill it as well ? if yes then presume it is from Col C in Data ?
2) What about the rest of columns in Working I see these are formulas ...

gowflow
0
 
LVL 30

Expert Comment

by:gowflow
ID: 40557445
Again it seems you are busy but I got my way around.

Pls check the attached file as I assumed that the data in Data will always start at row 4 and data in Working will always start at row 6 and columns are fixed.

If it is anything different then will adapt to your request.

Here is the code

Sub GetData()
Dim WSD As Worksheet
Dim WSW As Worksheet
Dim MaxRowD As Long, MaxColD As Long, MaxRowW As Long, MaxColW As Long, I As Long, J As Long, K As Long
Dim MinRowD As Long, MinColD As Long, MinRowW As Long, MinColW As Long
Dim dTemp

'---> Set Variables
Set WSW = Sheets("Working")
MinRowW = 6
MaxRowW = WSW.Range("C" & WSW.Rows.Count).End(xlUp).Row
MaxColW = WSW.Cells(MinRowW - 1, WSW.Columns.Count).End(xlToLeft).Column
MinColW = WSW.Cells(MinRowW - 1, MaxColW).End(xlToLeft).Column


Set WSD = Sheets("Data")
MinRowD = 4
MaxRowD = WSD.Range("C" & WSD.Rows.Count).End(xlUp).Row
MaxColD = WSD.Cells(MinRowD - 1, WSD.Columns.Count).End(xlToLeft).Column
MinColD = WSD.Cells(MinRowD - 1, MaxColD).End(xlToLeft).Column


'---> Clear old data
WSW.Range("C" & MinRowW & ":C" & MaxRowW).ClearContents
WSW.Range("AH" & MinRowW & ":BH" & MaxRowW).ClearContents

For I = MinRowD To MaxRowD
    K = 0
    WSW.Cells(I + (MinRowW - MinRowD), "C") = WSD.Cells(I, "C")
    For J = MinColD To MaxColD
        dTemp = Split(WSD.Cells(I, J), "/")
        WSW.Cells(I + (MinRowW - MinRowD), J + K + (MinColW - MinColD)) = dTemp(0)
        WSW.Cells(I + (MinRowW - MinRowD), J + K + (MinColW - MinColD) + 2) = dTemp(1)
        K = K + 2
    Next J
Next I

MsgBox "Data Transfered successfully.", vbInformation

End Sub

Open in new window


Check the attached file as I deleted the data in working (kept the formulas) and run the button Get Data and see if all is ok.

gowflow
PROBLEM-V01.xlsm
0
Don't Miss ATEN at InfoComm 2017!

Visit booth #2167 to see the  new ATEN VM3200 32 x 32 Modular Matrix Switch. Other highlights include the VE8950 4K HDMI Over IP Extender, VS1912 12-Port DP Video Wall Media Player  and VK2100 ATEN Control System. Register now with Free Pass Code ATEN288!

 

Author Comment

by:DougDodge
ID: 40557719
Columns and rows are static.....

Attached is a little more detailed example.

The "Tracking" worksheet is the one that is deleted and replaced all the time..... "MC Tracker Data" is static in the control workbook.
PROBLEM.xlsx
0
 

Author Comment

by:DougDodge
ID: 40557722
More detail.....
0
 
LVL 30

Expert Comment

by:gowflow
ID: 40557821
What is it a test to see if I pass classes in programming ???

Why didn't you post the correct workbook at the first place ???

gowflow
0
 
LVL 30

Expert Comment

by:gowflow
ID: 40566321
Is this what you want ? I adapted the code in the last workbook that you posted.
Pls check and let me know if it is fine.

gowflow
0
 
LVL 30

Accepted Solution

by:
gowflow earned 500 total points
ID: 40570095
Ooops I just noticed I did not post the workbook !!!! what a fool I am :( :( soso sorry

here it is.

Pls chk and let me know.
gowflow
PROBLEM-V02.xlsm
0
 

Author Comment

by:DougDodge
ID: 40570182
Works perfectly.....
0
 

Author Comment

by:DougDodge
ID: 40570322
I've requested that this question be closed as follows:

Accepted answer: 0 points for DougDodge's comment #a40570182

for the following reason:

Exactly what I needed..... Thank you.
0
 
LVL 30

Expert Comment

by:gowflow
ID: 40570323
I think you closed this question by mistake as you attributed yourself 0 points and acknowledged that The solution that I provided works perfectly.

I activated an objection to this closure so you could correct the closing.
gowflow
0
 
LVL 30

Expert Comment

by:gowflow
ID: 40570712
Great !!! :)
gowlfow
0
 

Author Comment

by:DougDodge
ID: 40570899
I think I corrected this now..... Amazing, a site with programmers and this is one of the worst sites to navigate and work with....
0
 
LVL 30

Expert Comment

by:gowflow
ID: 40571015
I do not understand your comment:


this is one of the worst sites to navigate and work with....
0
 

Author Comment

by:DougDodge
ID: 40571154
Hard to do searches.
Notifications button keeps disappearing. It is there when my mouse is over the place it should be, but it rarely shows.
And you I have had issues over closing questions without awarding points correctly.
0
 
LVL 30

Expert Comment

by:gowflow
ID: 40572130
ok no problem don't worry you can ask me I will be glad to help. Pls post here any link to any new question you may need help with. We had a rough start at the beginning but after I guess things went smooth.

Hv a good day
gowflow
0

Featured Post

Flexible connectivity for any environment

The KE6900 series can extend and deploy computers with high definition displays across multiple stations in a variety of applications that suit any environment. Expand computer use to stations across multiple rooms with dynamic access.

Question has a verified solution.

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

Suggested Solutions

I recently resolved a client's Office 2013 installation problem and wanted to offer an observation that may help you with troubleshooting similar issues. The client ordered three Dell Optiplex system units with the Windows 7 downgrade option inst…
As freelancing is becoming more and more common in the tech industry, certain obstacles are proving to be a challenge to those who are used to more traditional, structured employment. This article is meant to help identify such obstacles and offer a…
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.

739 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