Macro help - format data as one row for each value in many columns

J

jayhill

New Member
Joined
Apr 18, 2016
Messages
10
Hi All,

I'm a little stumped on how to format some data coming out of an accounting report. The report contains a column of social security numbers and separate columns for a number of benefits for which each employee may have deductions.

I would like format this data such that for each nonzero amount in any of the benefit columns I create a separate row including the benefit name, ssn, and benefit deduction amount.

Can you point me in the right direction?

Thanks!
-jay


Unknown
[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH]Row\Col[/TH]
[TH]
A​
[/TH]
[TH]
B​
[/TH]
[TH]
C​
[/TH]
[TH]
D​
[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
1​
[/TD]
[TD="bgcolor: #D9D9D9"]Social[/TD]
[TD="bgcolor: #D9D9D9"]Benefit 1[/TD]
[TD="bgcolor: #D9D9D9"]Benefit 2[/TD]
[TD="bgcolor: #D9D9D9"]Benefit 3[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
2​
[/TD]
[TD]111-11-1111[/TD]
[TD]
270.84​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
3​
[/TD]
[TD]222-22-2222[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
4​
[/TD]
[TD]333-33-3333[/TD]
[TD]
37.5​
[/TD]
[TD]
1​
[/TD]
[TD]
22.75​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
5​
[/TD]
[TD]444-44-4444[/TD]
[TD]
20.83​
[/TD]
[TD]
1​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
6​
[/TD]
[TD]555-55-5555[/TD]
[TD]
62.5​
[/TD]
[TD]
1​
[/TD]
[TD]
39.15​
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet1[/TD]
[/TR]
</tbody>[/TABLE]


Unknown
[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH]Row\Col[/TH]
[TH]
A​
[/TH]
[TH]
B​
[/TH]
[TH]
C​
[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
1​
[/TD]
[TD]Benefit 1[/TD]
[TD]111-11-1111[/TD]
[TD]
270.84​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
2​
[/TD]
[TD]Benefit 1[/TD]
[TD]333-33-3333[/TD]
[TD]
37.5​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
3​
[/TD]
[TD]Benefit 2[/TD]
[TD]333-33-3333[/TD]
[TD]
1​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
4​
[/TD]
[TD]Benefit 3[/TD]
[TD]333-33-3333[/TD]
[TD]
22.75​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
5​
[/TD]
[TD]Benefit 1[/TD]
[TD]444-44-4444[/TD]
[TD]
20.83​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
6​
[/TD]
[TD]Benefit 2[/TD]
[TD]444-44-4444[/TD]
[TD]
1​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
7​
[/TD]
[TD]Benefit 1[/TD]
[TD]555-55-5555[/TD]
[TD]
62.5​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
8​
[/TD]
[TD]Benefit 2[/TD]
[TD]555-55-5555[/TD]
[TD]
1​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
9​
[/TD]
[TD]Benefit 3[/TD]
[TD]555-55-5555[/TD]
[TD]
39.15​
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet2[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
Welcome to the forum.

You don't necessarily need a macro. You can try something like this. With Sheet1 defined like this:

ABCD
SSNBenefit 1Benefit 2Benefit 3
111-11-1111
222-22-2222
333-33-3333
444-44-4444
555-55-5555

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]270.84[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]37.5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]22.75[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]20.83[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]62.5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]39.15[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​



your Sheet2 can look like this:

ABCDE
BenefitSSNAmountHelper
Benefit 1111-11-1111
Benefit 1333-33-3333
Benefit 2333-33-3333
Benefit 3333-33-3333
Benefit 1444-44-4444
Benefit 2444-44-4444
Benefit 1555-55-5555
Benefit 2555-55-5555
Benefit 3555-55-5555

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]270.84[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2.2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]37.5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4.2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4.3[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]22.75[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4.4[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]20.83[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5.2[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5.3[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]62.5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6.2[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6.3[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]39.15[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6.4[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A2[/TH]
[TD="align: left"]=IF(E2="","",INDEX(Sheet1!$B$1:$D$1,(E2-INT(E2))*10-1))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=IF(E2="","",INDEX(Sheet1!$A$2:$A$6,E2-1))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=IF(E2="","",INDEX(Sheet1!$A$1:$D$6,E2,(E2-INT(E2))*10))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]{=IF(E1="","",IFERROR(SMALL(IF(Sheet1!$B$2:$D$6>0,ROW($B$2:$D$6)+COLUMN($B$2:$D$6)/10),ROWS($E$2:$E2)),""))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Column E is a helper column, which looks for non-zero amounts and saves the location in the form ROW.COLUMN. You can hide this column if you like. Then the A, B, and C formulas look up the values from Sheet1 using the location in E. Enter the formulas, then drag them down the columns as far as needed. If you have more than 9 benefits, the formulas will have to be tweaked a little.

Let me know if this works for you.
 
Last edited:
Upvote 0
That's great! Thank you!

I didn't want to post a huge sample file for sheet1 so I truncated it. The production report that will generate sheet1 currently has 11 columns and may grow to 15. Is it possible to accommodate this?

Thanks again!
 
Upvote 0
The A2, C2, and E2 formulas all have the number 10 in them. Change that to 100 in all cases, and they'll handle up to 99 columns.

Glad to help!
 
Upvote 0
jayhill,

Welcome to the MrExcel forum.

Here is a macro solution for you to consider, that uses two arrays in memory, and, will adjust to the number of raw data rows, and, columns in Sheet1.

Sample raw data in worksheet Sheet1:


Excel 2007
ABCDE
1SocialBenefit 1Benefit 2Benefit 3
2111-11-1111270.8400
3222-22-2222000
4333-33-333337.5122.75
5444-44-444420.8310
6555-55-555562.5139.15
7
Sheet1


And, after the macro in worksheet Sheet2:


Excel 2007
ABC
1Benefit 1111-11-1111270.84
2Benefit 1333-33-333337.5
3Benefit 2333-33-33331
4Benefit 3333-33-333322.75
5Benefit 1444-44-444420.83
6Benefit 2444-44-44441
7Benefit 1555-55-555562.5
8Benefit 2555-55-55551
9Benefit 3555-55-555539.15
10
Sheet2


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
2. Open your NEW 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
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Sub ReorgData()
' hiker95, 04/18/2016, ME935611
Dim a As Variant, i As Long
Dim o As Variant, j As Long
Dim lr As Long, lc As Long, n As Long, c As Long
Application.ScreenUpdating = False
With Sheets("Sheet1")
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  a = .Range(.Cells(1, 1), .Cells(lr, lc)).Value
  n = Application.CountIf(.Range(.Cells(2, 2), .Cells(lr, lc)), "<>0")
  ReDim o(1 To n, 1 To 3)
End With
For i = 2 To UBound(a, 1)
  For c = 2 To UBound(a, 2)
    If a(i, c) <> 0 Then
      j = j + 1: o(j, 1) = a(1, c): o(j, 2) = a(i, 1): o(j, 3) = a(i, c)
    End If
  Next c
Next i
With Sheets("Sheet2")
  .Cells(1).CurrentRegion.ClearContents
  .Cells(1).Resize(UBound(o, 1), UBound(o, 2)) = o
  .Columns("A:C").AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorgData macro.
 
Last edited:
Upvote 0
jayhill,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Question Question
Is it possible to know which file it came from?
Replies
1
Views
355
alansidman
A
A
  • Question Question
Data Selection Based on Closest Value [20% Tolerance]
Replies
1
Views
196
StephenCrump
S
T
  • Solved
Index match with multiple criteria, sheets & columns
2
Replies
10
Views
717
Fluff
Fluff
W
  • Solved
How many rows between each data point in a column
Replies
3
Views
547
Fluff
Fluff
J
  • Solved
how to random all numbers
Replies
7
Views
260
Fluff
Fluff

Forum statistics

Threads
1,223,228
Messages
6,170,875
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top