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

[jayhill]

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

Row\Col	A​	B​	C​	D​
1​	Social	Benefit 1	Benefit 2	Benefit 3
2​	111-11-1111	270.84​	0​	0​
3​	222-22-2222	0​	0​	0​
4​	333-33-3333	37.5​	1​	22.75​
5​	444-44-4444	20.83​	1​	0​
6​	555-55-5555	62.5​	1​	39.15​

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

Unknown

Row\Col	A​	B​	C​
1​	Benefit 1	111-11-1111	270.84​
2​	Benefit 1	333-33-3333	37.5​
3​	Benefit 2	333-33-3333	1​
4​	Benefit 3	333-33-3333	22.75​
5​	Benefit 1	444-44-4444	20.83​
6​	Benefit 2	444-44-4444	1​
7​	Benefit 1	555-55-5555	62.5​
8​	Benefit 2	555-55-5555	1​
9​	Benefit 3	555-55-5555	39.15​

<tbody>
</tbody>

Sheet: Sheet2

<tbody>
</tbody>

 

[Eric W]

Welcome to the forum.

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

	A	B	C	D
1	SSN	Benefit 1	Benefit 2	Benefit 3
2	111-11-1111	270.84	0	0
3	222-22-2222	0	0	0
4	333-33-3333	37.5	1	22.75
5	444-44-4444	20.83	1	0
6	555-55-5555	62.5	1	39.15
7

<tbody>
</tbody>

Sheet1​

your Sheet2 can look like this:

	A	B	C	D	E
1	Benefit	SSN	Amount		Helper
2	Benefit 1	111-11-1111	270.84		2.2
3	Benefit 1	333-33-3333	37.5		4.2
4	Benefit 2	333-33-3333	1		4.3
5	Benefit 3	333-33-3333	22.75		4.4
6	Benefit 1	444-44-4444	20.83		5.2
7	Benefit 2	444-44-4444	1		5.3
8	Benefit 1	555-55-5555	62.5		6.2
9	Benefit 2	555-55-5555	1		6.3
10	Benefit 3	555-55-5555	39.15		6.4
11

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula A2 =IF(E2="","",INDEX(Sheet1!$B$1:$D$1,(E2-INT(E2))*10-1)) B2 =IF(E2="","",INDEX(Sheet1!$A$2:$A$6,E2-1)) C2 =IF(E2="","",INDEX(Sheet1!$A$1:$D$6,E2,(E2-INT(E2))*10)) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula E2 {=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)),""))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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.

 

[jayhill]

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!

 

[Eric W]

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!

 

[hiker95]

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
	A	B	C	D	E
1	Social	Benefit 1	Benefit 2	Benefit 3
2	111-11-1111	270.84	0	0
3	222-22-2222	0	0	0
4	333-33-3333	37.5	1	22.75
5	444-44-4444	20.83	1	0
6	555-55-5555	62.5	1	39.15
7
Sheet1

And, after the macro in worksheet Sheet2:

Excel 2007
	A	B	C
1	Benefit 1	111-11-1111	270.84
2	Benefit 1	333-33-3333	37.5
3	Benefit 2	333-33-3333	1
4	Benefit 3	333-33-3333	22.75
5	Benefit 1	444-44-4444	20.83
6	Benefit 2	444-44-4444	1
7	Benefit 1	555-55-5555	62.5
8	Benefit 2	555-55-5555	1
9	Benefit 3	555-55-5555	39.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.

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.

 

[jayhill]

Big thanks to you both for two awesome solutions to the problem!

 

[hiker95]

jayhill,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.