convert matrix worrk sheet to a flat file

L

lefty38

Board Regular
Joined
Oct 27, 2005
Messages
85
I have a matrix file with 83 comlumns and 21,000+ employees
In column A returns the employee ID#
Column B returns Column headers
Column C Returns the Value in the field

field values of : Complete; "NR"; or NULL.
---------------------------------------------------------------------------
Employee Id</SPAN>
Item 1</SPAN>
Item 2</SPAN>
Item 3</SPAN>
Item 4</SPAN>
Item 5</SPAN>
Item 6</SPAN>
Item 7</SPAN>
Item 8</SPAN>
Item 9</SPAN>
Item 10</SPAN>
Item 11</SPAN>
Item 12</SPAN>
1555</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
Complete</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
1563</SPAN>
NR</SPAN>
NR</SPAN>
Complete</SPAN>
NR</SPAN>
Complete</SPAN>
Complete</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
1652</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
1688</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
Complete</SPAN>
Complete</SPAN>
NR</SPAN>
NR</SPAN>
Complete</SPAN>
Complete</SPAN>
1708</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
1734</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
NR</SPAN>
1809</SPAN>
NR</SPAN>
NR</SPAN>
Complete</SPAN>
Complete</SPAN>
NR</SPAN>
Complete</SPAN>
Complete</SPAN>
Complete</SPAN>
NR</SPAN>
NR</SPAN>

<TBODY>
</TBODY>

----------------------------------------------------------------------------
RESULTS from VB code below
Row
Column
Value
1555
Item 1
NR
1555
Item 2
NR
1555
Item 3
NR
1555
Item 4
NR
1555
Item 5
NR
1555
Item 6
NR
1555
Item 7
Complete
1555
Item 8
NR
1555
Item 9
NR
1555
Item 10
NR
1555
Item 11
NR
1555
Item 12
NR
1563
Item 1
NR
1563
Item 2
NR
1563
Item 3
Complete
1563
Item 4
NR
1563
Item 5
Complete
1563
Item 6
Complete
1563
Item 7
NR
1563
Item 8
NR
1563
Item 9
NR
1563
Item 10
1563
Item 11
NR
1563
Item 12
NR
1652
Item 1
NR

<TBODY>
</TBODY>
----------------------------------------------------------------------------
VB Script I found on the web

Code: 
Sub NormaliseTable() 
' start with the cursor in the table 
  Dim rTab As Range, C As Range, rNext As Range 
  Set rTab = ActiveCell.CurrentRegion 
  If rTab.Rows.Count=1 Or rTab.Columns.Count = 1 Then 
    MsgBox "Not a well-formed table!" 
    Exit Sub 
  End If 
  Worksheets.Add  ' the sheet for the results 
  Range("A1:C1") = Array("Row","Column","Value") 
  Set rNext = Range("A2") 
  For Each C In rTab.Offset(1,1).Resize(rTab.Rows.Count-1, _ 
         rTab.Columns.Count-1).Cells 
    If Not IsEmpty(C.Value) Then 
      rNext.Value = rTab.Cells(C.Row-rTab.Row+1,1) 
      rNext.Offset(0,1).Value = rTab.Cells(1,C.Column-rTab.Column+1)     
      rNext.Offset(0,2).Value = C.Value 
      Set rNext = rNext.Offset(1,0) 
    End If 
  Next 
End Sub


Problem is: The script exceeds excels 1 million rows -

How can the VB Code be adjusted to exlude the "NR" values on the new worksheet?
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
I haven't looked closely, but try perhaps changing:
If Not (IsEmpty(C.Value) Then
to:
If Not (IsEmpty(C.Value) Or C.Value = "NR") Then
 
Upvote 0
thank you P45cAL I applied your change but for some reason it is only returning the "completed" values

------------------------------------------------------------------------------
Sub NormaliseTable()
' start with the cursor in the table
Dim rTab As Range, C As Range, rNext As Range
Set rTab = ActiveCell.CurrentRegion
If rTab.Rows.Count = 1 Or rTab.Columns.Count = 1 Then
MsgBox "Not a well-formed table!"
Exit Sub
End If
Worksheets.Add ' the sheet for the results
Range("A1:C1") = Array("Row", "Column", "Value")
Set rNext = Range("A2")
For Each C In rTab.Offset(1, 1).Resize(rTab.Rows.Count - 1, _
rTab.Columns.Count - 1).Cells
If Not (IsEmpty(C.Value) Or C.Value = "NR") Then
rNext.Value = rTab.Cells(C.Row - rTab.Row + 1, 1)
rNext.Offset(0, 1).Value = rTab.Cells(1, C.Column - rTab.Column + 1)
rNext.Offset(0, 2).Value = C.Value
Set rNext = rNext.Offset(1, 0)
End If
Next
End Sub
---------------------------------------------------------------------------
 
Upvote 0
lefty38 said:
I applied your change but for some reason it is only returning the "completed" values
Click to expand...
Well, since you stated:
lefty38 said:
field values of : Complete; "NR"; or NULL
Click to expand...
and your original code excluded blanks (you call them Null):
lefty38 said:
If Not IsEmpty(C.Value) Then
Click to expand...
and you wanted
lefty38 said:
How can the VB Code be adjusted to exlude the "NR" values on the new worksheet?
Click to expand...
Then that doesn't leave anything but "Complete".
 
Upvote 0
I came across this thread and decided to try the VBA module to address my problem (very similar to the original post). The code worked (with some caveats) but I then discovered a fault in my data, corrected it (I deleted a partial column at the end of my array). Since then, the macro won't run. i.e. Excel hangs and I can see at the bottom of my screen a repeating "calculating" message as if the code is caught in a loop. Interestingly, when I step through the code it works although as there are 30k rows I reset this after a few rows. Can anyone suggest anything?
Many thanks,
Denis
 
Upvote 0
Item 1Item 2Item 3Item 4Item 5Item 6Item 7Item 8Item 9Item 10Item 11Item 12
1555NRNRNRNRNRNRCompleteNRNRNRNRNR
1563NRNRCompleteNRCompleteCompleteNRNRNRNRNR
1652NRNRNRNRNRNRNRNRNRNR
1688NRNRNRCompleteCompleteNRNRCompleteComplete
1708NRNRNRNRNRNRNRNRNRNRNRNR
1734NRNRNRNRNRNRNR
why not use a simple macro
1555NRrrow = 14
1555NR For j = 2 To 7
1555NR For k = 2 To 13
1555NR rrow = rrow + 1
1555NR Cells(rrow, 1) = Cells(j, 1)
1555NR Cells(rrow, 2) = Cells(j, k)
1555Complete Next k
1555NR Next j
1555NREnd Sub
1555NR
1555NR
1555NR
1563NR
1563NR
1563Complete
1563NR
1563Complete
1563Complete
1563NR
1563NR
1563NR
1563continues down to 1734 NR

<colgroup><col><col span="13"></colgroup><tbody>
</tbody>
 
Upvote 0
I have been using this code quite successfully - thanks all! I now have another challenge that I cannot solve alone. I need to bring data from the first TWO columns along into the data table and look for the rest.
Can anyone suggest a way to adjust this code to bring along the first two columns and search for the rest?

Thanks.
 
Upvote 0
From this:

Project Status Oct-16 Nov-16 Dec-16 Jan-17 Feb-17
D4567 APRV $4000 $3000 $12000 $8500 $1350
D4567 UNAP $10000 $6754 $6000
V6574 UNAP $7000 $8000 $4500


To this:
PROJECT STATUS MONTH VALUE
D4567 APRV Oct-16 $4000
D4567 APRV Nov-16 $3000
D4567 APRV Dec-16 $12000
D4567 APRV Jan-17 $8500
D4567 APRV Feb-17 $1350
D4567 UNAP Dec-16 $10000
D4567 UNAP Jan-17 $8500
D4567 UNAP Feb-17 $6000
V6574 UNAP Nov-16 $7000
V6574 UNAP Jan-17 $8000
V6574 UNAP Feb-17 $4500

Number of Rows and Months will always be variable, but the end data requires four columns.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,214,932
Messages
6,122,332
Members
449,077
Latest member
jmsotelo

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