Help formatting report

jacob940

New Member
Joined
Nov 5, 2011
Messages
3
I am trying to format data into a report. I've tried using VLOOKUP, arrays, If statement. What I get is blank lines if Vlookup does not find a match for "one" for example. Can someone please help?
Note: I cannot use pivot table in this. I also have excel 2003 and cannot filter multiple rows.

Thanks..

<table border="0" cellpadding="0" cellspacing="0" width="492"><colgroup><col style="mso-width-source:userset;mso-width-alt:5522;width:113pt" width="151"> <col style="mso-width-source:userset;mso-width-alt:4900;width:101pt" width="134"> <col style="mso-width-source:userset;mso-width-alt:5229;width:107pt" width="143"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;width:113pt" height="17" width="151">CATEGORY</td> <td class="xl24" style="width:101pt" width="134">NAME</td> <td class="xl24" style="width:107pt" width="143">AGE</td> <td class="xl24" style="width:48pt" width="64">Divorced</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">1</td> <td class="xl24">Jacob</td> <td class="xl24">44</td> <td class="xl25" align="right">5/4/1945</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">3</td> <td class="xl24">John</td> <td class="xl24">22</td> <td class="xl25" align="right">6/8/1966</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">4</td> <td class="xl24">Cynthia</td> <td class="xl24">25</td> <td class="xl25" align="right">9/4/1988</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">5</td> <td class="xl24">Mary</td> <td class="xl24">70</td> <td class="xl25" align="right">9/9/1974</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">1</td> <td class="xl24">Bob</td> <td class="xl24">55</td> <td class="xl25" align="right">6/8/1945</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">1</td> <td class="xl24">Joe</td> <td class="xl24">44</td> <td class="xl25" align="right">6/4/1999</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">1</td> <td class="xl24">Jacob</td> <td class="xl24">34</td> <td class="xl25" align="right">3/5/1974</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">3</td> <td class="xl24">Bethany</td> <td class="xl24">76</td> <td class="xl25" align="right">5/8/1966</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">4</td> <td class="xl24">Curtis</td> <td class="xl24">35</td> <td class="xl25" align="right">6/7/1998</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">4</td> <td class="xl24">Pearl</td> <td class="xl24">32</td> <td class="xl25" align="right">8/4/2006</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">3</td> <td class="xl24">Jamica</td> <td class="xl24">32</td> <td class="xl25" align="right">8/8/1972</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">1</td> <td class="xl24">Garry</td> <td class="xl24">22</td> <td class="xl25" align="right">2/22/1961</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">One</td> <td class="xl24">
</td> <td class="xl24">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">1</td> <td class="xl24">Jacob</td> <td class="xl24">44</td> <td class="xl25" align="right">5/4/1945</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">1</td> <td class="xl24">Bob</td> <td class="xl24">55</td> <td class="xl25" align="right">6/8/1945</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">1</td> <td class="xl24">Joe</td> <td class="xl24">44</td> <td class="xl25" align="right">6/4/1999</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">1</td> <td class="xl24">Jacob</td> <td class="xl24">34</td> <td class="xl25" align="right">3/5/1974</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">1</td> <td class="xl24">Garry</td> <td class="xl24">22</td> <td class="xl25" align="right">2/22/1961</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">Three</td> <td class="xl24">
</td> <td class="xl24">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">3</td> <td class="xl24">John</td> <td class="xl24">22</td> <td class="xl25" align="right">6/8/1966</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">3</td> <td class="xl24">Bethany</td> <td class="xl24">76</td> <td class="xl25" align="right">5/8/1966</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">3</td> <td class="xl24">Jamica</td> <td class="xl24">32</td> <td class="xl25" align="right">8/8/1972</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">Four</td> <td class="xl24">
</td> <td class="xl24">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">4</td> <td class="xl24">Cynthia</td> <td class="xl24">25</td> <td class="xl25" align="right">9/4/1988</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">4</td> <td class="xl24">Curtis</td> <td class="xl24">35</td> <td class="xl25" align="right">6/7/1998</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">4</td> <td class="xl24">Pearl</td> <td class="xl24">32</td> <td class="xl25" align="right">8/4/2006</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">Five</td> <td class="xl24">
</td> <td class="xl24">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">5</td> <td class="xl24">Mary</td> <td class="xl24">70</td> <td class="xl25" align="right">9/9/1974</td> </tr> </tbody></table>
 
Last edited:

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,480
Welcome to the Board;

I could write you the VBA code (Macro) to accompllish this task.
Is the data you have provided the "normal" limit of your real data, or
have you presented only a representative sample and the actual data
is far more complex (going up to 25 or 100 categories)?

Please advise,

Jim May
 

jacob940

New Member
Joined
Nov 5, 2011
Messages
3
It's just a representative sample..

I tried VB, but I'm only beginner.
I have something like this..

Sub Macro1()
Dim rng As Range
For Each Row In Range("A2:D13")
If Row.Value = "1" Then
Row.Select
Selection.Copy
End If
Exit For
Next Row
End Sub

ultimately, I would like the report to be on another spreadsheet.
i guess i would need a loop statement too?
 
Last edited:

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,480
To learn myself I'm going to work on this today (Don't hold your breath til I report back)...

Jim
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,480
Before running Macro:
Excel Workbook
ABCDEFGHIJ
1CATEGORYNAMEAGEDivorced
21Jacob445/4/1945
33John226/8/1966
44Cynthia259/4/1988
55Mary709/9/1974
61Bob556/8/1945
71Joe446/4/1999
81Jacob343/5/1974
93Bethany765/8/1966
104Curtis356/7/1998
114Pearl328/4/2006
123Jamica328/8/1972
131Garry222/22/1961
Sheet1
Excel 2007

Paste All this code into a Standard Module:

Code:
Option Base 1
Dim MyRng As Range

Sub PresentData()
Dim LR As Long, Jump As Long
Dim MyRng As Range
Set MyRng = ActiveSheet.Range("a2", ActiveSheet.Range("a2").End(xlDown))
LR = MyRng.Rows.Count + 1
Application.Calculation = xlCalculationManual
MyRng.Select
MyRng.Copy Destination:=Range("M1")   'This should be a Clear - Open Free Range of Cells to receive this paste
Range("M1:M" & LR - 1).Select
Range("M1:M" & LR - 1).Offset(, 1).Formula = "=IF(UniqueItem($M$1:$M$13,ROW())=0,"""",UniqueItem($M$1:$M$13,ROW()))"
Call bubble_sort
Range("N1").Select
Jump = 1
Do Until ActiveCell.Value = ""
Cells(LR + 3, Jump).Value = WorksheetFunction.Choose(ActiveCell.Value, "One", "Two", "Three", "Four", "Five")
ActiveCell.Offset(1).Select
Jump = Jump + 3
Loop
nx1 = 0
nx2 = 0
nx3 = 0
nx4 = 0
nx5 = 0
For Each c In MyRng
    'Below I have only allowed for 4 unique Categories - If there are more they will need to be added and
    'appropriate settings established compatible with the 4 already provided
    Select Case c
    Case Is = Range("N" & 1).Value
    c.Offset(, 1).Resize(1, 3).Copy
    Range(Cells(LR + 4 + nx1, 1), Cells(LR + 4 + nx1, 3)).PasteSpecial xlPasteValues
    nx1 = nx1 + 1
    
    Case Is = Range("N" & 2).Value
    c.Offset(, 1).Resize(1, 3).Copy
    Range(Cells(LR + 4 + nx2, 4), Cells(LR + 4 + nx2, 6)).PasteSpecial xlPasteValues
    nx2 = nx2 + 1
    
    Case Is = Range("N" & 3).Value
    c.Offset(, 1).Resize(1, 3).Copy
    Range(Cells(LR + 4 + nx3, 7), Cells(LR + 4 + nx3, 9)).PasteSpecial xlPasteValues
    nx3 = nx3 + 1
    
    Case Is = Range("N" & 4).Value
    c.Offset(, 1).Resize(1, 3).Copy
    Range(Cells(LR + 4 + nx4, 10), Cells(LR + 4 + nx4, 12)).PasteSpecial xlPasteValues
    nx4 = nx4 + 1
    End Select
Next c
Application.CutCopyMode = False
Application.Calculation = xlCalculationAutomatic
End Sub

Private Sub bubble_sort()
Dim sortingArray As Variant, i As Long, j As Long, temp As Variant

 sortingArray = Selection

 For i = 1 To (UBound(sortingArray, 1) - 1)
    For j = i To UBound(sortingArray, 1)
       If Val(sortingArray(j, 1))< Val(sortingArray(i, 1)) Then
          temp = sortingArray(i, 1)
          sortingArray(i, 1) = sortingArray(j, 1)
          sortingArray(j, 1) = temp
       End If
    Next j
 Next i

 Selection = sortingArray

 End Sub

Function UniqueItem(InputRange As Range, ItemNo As Long) As Variant
Application.Volatile
Dim a, e, n As Long
a = InputRange.Columns(1).Value
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For Each e In a
        If (Not IsEmpty(e)) * (Not .exists(e)) Then
            n = n + 1
            If n = ItemNo Then
                UniqueItem = e
                Exit Function
            End If
            .Add e, Nothing
        End If
    Next
End With
End Function

Run the Macro "Presentdata" and sheet should then look like this:
Data in Range N1:N12 didn't copy over as the HTML poster doesn't go beyond Column M (Sorry). Format the Date Fields - I failed to do so in
the VBA - should have but didn;t.....

Write back with comments

Jim
Excel Workbook
ABCDEFGHIJKLM
1CATEGORYNAMEAGEDivorced1
21Jacob445/4/19451
33John226/8/19661
44Cynthia259/4/19881
55Mary709/9/19741
61Bob556/8/19453
71Joe446/4/19993
81Jacob343/5/19743
93Bethany765/8/19664
104Curtis356/7/19984
114Pearl328/4/20064
123Jamica328/8/19725
131Garry222/22/1961
14
15
16OneThreeFourFive
17Jacob4416561John2224266Cynthia2532390Mary7027281
18Bob5516596Bethany7624235Curtis3535953
19Joe4436315Jamica3226519Pearl3238933
20Jacob3427093
21Garry2222334
Sheet1
Excel 2007
 

jacob940

New Member
Joined
Nov 5, 2011
Messages
3
lots of thanks for taking the time to write vb script...

is there a way not to make it go horizontal. more specifically is there a way to paste the "one's" to certain cells (or on a different worksheet)
for example
"one" - worksheet 2 cell A25 and down
"two" - worksheet 2 cell A30 and down
"three" - worksheet 2 cell A35 and down.....
 

Watch MrExcel Video

Forum statistics

Threads
1,123,102
Messages
5,599,733
Members
414,332
Latest member
Hussain Almadani

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
Top