index Match to reformat data

I have several trackers that production employees fill out daily, that are in an ugly format for data anaylsis, but is convenient for their daily use.

I was wondering if someone could help me figure out a way to get the information into the format that I would like.

I would like the data to show in columns:

• Dates
• Last Name
• First Name
• Quantity
• Hours
• Avg

I am going to show the workbook, exactly as the Tracker is formatted, then there is an example of the format that I would like to get all the data.
I was thinking, maybe I could have this "Final Format" sheet in each workbook, but I am not sure.

Any ideas would be appreciated.

Thanks,
Staci

I would use a macro to create a new sheet and put the data into the correct format. Is that OK?

Also your sample results should include a few more days. We need to see how your "results" would treat employees that are blank on a day, and where whole days have no data. Show us those results, too.

Staci,

Detach/open workbook ReorgData w1 wF LR LC Count rng - Staci - EF768143 - SDG13.xls and run macro ReorgData.



The below macro will clear and/or create worksheet Final Format.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
' stanleydgromjr, 03/13/2011
' http://www.excelforum.com/excel-general/768143-index-match-to-reformat-data.html
Dim w1 As Worksheet, wF As Worksheet
Dim LR As Long, LC As Long, NR As Long, a As Long, aa As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Tracker")
If Not Evaluate("ISREF('Final Format'!A1)") Then Worksheets.Add(After:=w1).Name = "Final Format"
Set wF = Worksheets("Final Format")
wF.UsedRange.Clear
wF.Range("A1:F1") = [{"Date","Last Name","First Name","Quantity","Hours","AVG."}]
LR = w1.Cells(Rows.Count, 1).End(xlUp).Row
LC = w1.Cells(3, Columns.Count).End(xlToLeft).Column
For a = 4 To LC Step 3
  For aa = 4 To LR Step 1
    If Application.Count(w1.Range(w1.Cells(aa, a), w1.Cells(aa, a + 2))) > 0 Then
      NR = wF.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
      wF.Cells(NR, 1).Value = w1.Cells(2, a).Value
      wF.Range("B" & NR).Resize(, 2).Value = w1.Range("A" & aa & ":B" & aa).Value
      wF.Range("D" & NR & ":F" & NR).Value = w1.Range(w1.Cells(aa, a), w1.Cells(aa, a + 2)).Value
    End If
  Next aa
Next a
wF.Range("A2:A" & NR).NumberFormat = "m/d/yyyy"
wF.Range("E2:F" & NR).NumberFormat = "0.00"
wF.Range("D2:F" & NR).HorizontalAlignment = xlCenter
wF.UsedRange.Columns.AutoFit
wF.Activate
Application.ScreenUpdating = True
End Sub

Then run the ReorgData macro.

Staci,

Do you want the macro to work on all worksheets in your workbook?

Or, just a selection of some of the worksheets? If so, what is the logic to just pick some of the worksheets? We could run the macro on all worksheets that begin with Tracker.

Do you want the Final Format worksheet to include all the data from the other worksheets?

Do you want the macro to work on all worksheets in your workbook?
No

Or, just a selection of some of the worksheets?
Yes

If so, what is the logic to just pick some of the worksheets?
still thinking about that one...lol

We could run the macro on all worksheets that begin with Tracker.
I could make this scenerio work and rename the worksheets to say "Trackerx", "Trackerxx", "Trackerxxx",etc.

Do you want the Final Format worksheet to include all the data from the other worksheets?
Trick Question: Well, Yes from all the worksheets that will start with Tracker

Will this macro, just pull the information once or will it duplicate the data if the macro is reran? Meaning today I run the macro, it will format the data, how it is currently being done. Tomorrow I rerun the macro the only difference is it will basically reset worksheet "Final Format", and give me the current information, correct? If so that would be brilliant.

Or will it duplicate the previous information, in addition to getting the current data? That would not be good.

Thanks for you help!

Staci,

Will this macro, just pull the information once or will it duplicate the data if the macro is reran? Meaning today I run the macro, it will format the data, how it is currently being done. Tomorrow I rerun the macro the only difference is it will basically reset worksheet "Final Format", and give me the current information, correct? If so that would be brilliant.

OK, I will update the macro to include the additional Tracker??? worksheets in your last posted workbook.

Staci,

Detach/open workbook ReorgData multiple Tracker ws LR LC Count rng - Staci - EF768143 - SDG13.xls and run macro ReorgDataV2.

Staci,


The following will work correctly as long as the three additional columns Quantity, Hours, and Avg. are in each Tracker??? worksheet.



Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgDataV3()
' stanleydgromjr, 03/16/2011
' http://www.excelforum.com/excel-general/768143-index-match-to-reformat-data.html
Dim ws As Worksheet, wF As Worksheet
Dim LR As Long, LC As Long, NR As Long, a As Long, aa As Long
Application.ScreenUpdating = False
If Not Evaluate("ISREF('Final Format'!A1)") Then Worksheets.Add(Before:=Worksheets(1)).Name = "Final Format"
Set wF = Worksheets("Final Format")
wF.UsedRange.Clear
wF.Range("A1:F1") = [{"Date","Last Name","First Name","Quantity","Hours","AVG."}]
For Each ws In ThisWorkbook.Worksheets
  If Left(ws.Name, 7) = "Tracker" Then
    With ws
      LR = ws.Cells(Rows.Count, 1).End(xlUp).Row
      LC = ws.Cells(3, Columns.Count).End(xlToLeft).Column - 3
      For a = 4 To LC Step 3
        For aa = 4 To LR Step 1
          If Application.Count(ws.Range(ws.Cells(aa, a), ws.Cells(aa, a + 2))) > 0 Then
            NR = wF.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
            wF.Cells(NR, 1).Value = ws.Cells(2, a).Value
            wF.Range("B" & NR).Resize(, 2).Value = ws.Range("A" & aa & ":B" & aa).Value
            wF.Range("D" & NR & ":F" & NR).Value = ws.Range(ws.Cells(aa, a), ws.Cells(aa, a + 2)).Value
          End If
        Next aa
      Next a
    End With
  End If
Next ws
LR = wF.Cells(Rows.Count, 1).End(xlUp).Row
wF.Range("A2:A" & LR).NumberFormat = "m/d/yyyy"
wF.Range("E2:F" & LR).NumberFormat = "0.00"
wF.Range("D2:F" & LR).HorizontalAlignment = xlCenter
wF.UsedRange.Columns.AutoFit
wF.Activate
Application.ScreenUpdating = True
End Sub

Then run the ReorgDataV3 macro.

Staci,

The following will work correctly if your Tracker??? worksheets contain or do not contain, the three additional columns Quantity, Hours, and Avg. in row 3, or Total in row 2.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgDataV4()
' stanleydgromjr, 03/17/2011
' http://www.excelforum.com/excel-general/768143-index-match-to-reformat-data.html
Dim ws As Worksheet, wF As Worksheet
Dim LR As Long, LC As Long, NR As Long, a As Long, aa As Long, MyT As Long
Application.ScreenUpdating = False
If Not Evaluate("ISREF('Final Format'!A1)") Then Worksheets.Add(Before:=Worksheets(1)).Name = "Final Format"
Set wF = Worksheets("Final Format")
wF.UsedRange.Clear
wF.Range("A1:F1") = [{"Date","Last Name","First Name","Quantity","Hours","AVG."}]
For Each ws In ThisWorkbook.Worksheets
  If Left(ws.Name, 7) = "Tracker" Then
    With ws
      LR = ws.Cells(Rows.Count, 1).End(xlUp).Row
      MyT = 0
      On Error Resume Next
      MyT = Application.Match("Total", ws.Rows(2), 0)
      On Error GoTo 0
      If MyT > 0 Then
        LC = MyT - 1
      Else
        LC = ws.Cells(3, Columns.Count).End(xlToLeft).Column
      End If
      For a = 4 To LC Step 3
        For aa = 4 To LR Step 1
          If Application.Count(ws.Range(ws.Cells(aa, a), ws.Cells(aa, a + 2))) > 0 Then
            NR = wF.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
            wF.Cells(NR, 1).Value = ws.Cells(2, a).Value
            wF.Range("B" & NR).Resize(, 2).Value = ws.Range("A" & aa & ":B" & aa).Value
            wF.Range("D" & NR & ":F" & NR).Value = ws.Range(ws.Cells(aa, a), ws.Cells(aa, a + 2)).Value
          End If
        Next aa
      Next a
    End With
  End If
Next ws
LR = wF.Cells(Rows.Count, 1).End(xlUp).Row
wF.Range("A2:A" & LR).NumberFormat = "m/d/yyyy"
wF.Range("E2:F" & LR).NumberFormat = "0.00"
wF.Range("D2:F" & LR).HorizontalAlignment = xlCenter
wF.UsedRange.Columns.AutoFit
wF.Activate
Application.ScreenUpdating = True
End Sub

Then run the ReorgDataV4 macro.

Staci,

Would you like to send me the actual file to look at?

See my Private Message to you (top right hand corner of the Excel Forum, Welcome, Staci., Private Messages:

I will have to post the the new code on the Excel Forum, but not the workbook if it contains sensative information.