Extracting non zero cells from a data table

J

JHE1969

New Member
Joined
Apr 2, 2018
Messages
11
I have a large data table that takes the form of n rows of Reference1 and z columns of Reference2, for each cell there is an output data that is numeric. Many output cells are zero are blank. I need to extract the data relating to the non-zero cells in the form of a simple 3 column multiple row table where the columns are Reference1, Reference2, Amount. An example Input Data Table and a fraction of the desired output data table is shown below.

[TABLE="width: 435"]
<tbody>[TR]
[TD="width: 87"]Input Table[/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]F00004[/TD]
[TD]F00005[/TD]
[TD]F00006[/TD]
[TD]F00007[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LB2000012[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] -[/TD]
[/TR]
[TR]
[TD]LB2000029[/TD]
[TD="class: xl63"] 21,072.70[/TD]
[TD="class: xl63"] 19,247.30[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] -[/TD]
[/TR]
[TR]
[TD]LB2000030[/TD]
[TD="class: xl63"] 19.62[/TD]
[TD="class: xl63"] 17.18[/TD]
[TD="class: xl63"] 3.81[/TD]
[TD="class: xl63"] 8.09[/TD]
[/TR]
[TR]
[TD]LB2000033[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] -[/TD]
[/TR]
[TR]
[TD]LB2000035[/TD]
[TD="class: xl63"] 9,626.08[/TD]
[TD="class: xl63"] 9,659.63[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] -[/TD]
[/TR]
[TR]
[TD]LB2000036[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] 2,250.00[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] -[/TD]
[/TR]
[TR]
[TD]LB2000039[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] -[/TD]
[/TR]
[TR]
[TD]LB2000040[/TD]
[TD="class: xl63"] 1,560.42[/TD]
[TD="class: xl63"] 1,569.71[/TD]
[TD="class: xl63"] 244.89[/TD]
[TD="class: xl63"] 187.09[/TD]
[/TR]
[TR]
[TD]LB2000042[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] -[/TD]
[/TR]
[TR]
[TD]LB2000043[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] -[/TD]
[/TR]
[TR]
[TD]LB2000044[/TD]
[TD="class: xl63"] 10,333.20[/TD]
[TD="class: xl63"] 10,368.64[/TD]
[TD="class: xl63"] 1,197.54[/TD]
[TD="class: xl63"] 1,197.54[/TD]
[/TR]
[TR]
[TD]LB2000045[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] -[/TD]
[/TR]
[TR]
[TD]LB2000047[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] -[/TD]
[/TR]
[TR]
[TD]LB2000048[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] -[/TD]
[/TR]
[TR]
[TD]LB2000050[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] -[/TD]
[/TR]
[TR]
[TD]LB2000052[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] -[/TD]
[TD="class: xl63"] -[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Desired Output Table[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD]Ref1[/TD]
[TD="class: xl63"] Ref2[/TD]
[TD="class: xl63"] Amount[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD]F00004[/TD]
[TD]LB2000029[/TD]
[TD="class: xl63"] 21,072.70[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD]F00004[/TD]
[TD]LB2000030[/TD]
[TD="class: xl63"] 19.62[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD]F00004[/TD]
[TD]LB2000035[/TD]
[TD="class: xl63"] 9,626.08[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD]F00004[/TD]
[TD]LB2000040[/TD]
[TD="class: xl63"] 1,560.42[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD]F00004[/TD]
[TD]LB2000044[/TD]
[TD="class: xl63"] 10,333.20[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD]F00005[/TD]
[TD]LB2000029[/TD]
[TD="class: xl63"] 19,247.30[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]F00005[/TD]
[TD]LB2000030[/TD]
[TD="class: xl63"] 17.18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]F00005[/TD]
[TD]LB2000035[/TD]
[TD="class: xl63"] 9,659.63[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]F00005[/TD]
[TD]LB2000040[/TD]
[TD="class: xl63"] 2,250.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]F00005[/TD]
[TD]LB2000044[/TD]
[TD="class: xl63"] 10,368.64[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
Not saying I'll answer this so this question is for the benefit of anyone that wants to solve this but VBA would be the best solution, would that suffice?
 
Upvote 0
Never used VBA so no idea, always happy to try. Was hoping there might be a clever formulaic solution. Thanks as always.
 
Upvote 0
Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro into the empty code window that opens up. Press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key. The output will be in Sheet2.
Code: 
Sub ExtractNonZero()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim lColumn As Long
    lColumn = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
    Dim x As Long, y As Long
    For y = 2 To lColumn
        For x = 2 To LastRow
            If Len(Sheets("Sheet1").Cells(x, y)) > 1 Then
                Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "A").End(xlUp).Offset(1, 0) = Sheets("Sheet1").Cells(1, y)
                Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "B").End(xlUp).Offset(1, 0) = Sheets("Sheet1").Cells(x, 1)
                Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "C").End(xlUp).Offset(1, 0) = Sheets("Sheet1").Cells(x, y)
            End If
        Next x
    Next y
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Generating a data summary output sheet from a from larger data input sheet
Replies
5
Views
276
dreid1011
dreid1011
K
  • Question Question
Find text & return next cell from non-adjacent table data
Replies
3
Views
198
katiecolorado
K
P
  • Question Question
Extract numeric value from X Point until next non numeric values
Replies
4
Views
425
kvsrinivasamurthy
K
P
  • Question Question
Copy non empty cells from the dynamic range column and paste to the bottom of data in table
Replies
0
Views
600
PitMax
P
K
  • Solved
Trying to extract specific values from a string of text in a cell - variable length
Replies
8
Views
652
kkoeb
K

Forum statistics

Threads
1,225,477
Messages
6,185,210
Members
453,283
Latest member
Shortm88

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