Create Report(?) to report on rows with empty cells

surkdidat

Active Member
Joined
Oct 1, 2011
Messages
359
(DATA TAB)
Internal Ref External Ref DETAILS DATE REC'D REQUESTOR AREA PRIORITY DEVELOPER STATUS STATUS2 STATUS DATE ANALYST Type Price Comments
1
1234 Request 101/06/2019 Adam1 ASteveStage 1 A03/06/2019Sophie2500 On Target
21235 Request 202/06/2019 Peter1 BPhilStage 1 B03/06/2019Ellie On Target
31236 Request 3 03/06/2019 Andy1 ATomStage 3 C05/06/2019 Kaitlyn6050
41237 Request 403/06/2019 Katie1 CStuartStage 4 B08/06/2019 Hannah

<tbody>
</tbody>

(Before Macro is Run This Table is completely empty of any data)
(REPORT TAB)


<tbody style="border-collapse: collapse; width: auto;">
</tbody>


MACRO IS NOW RUN.



(REPORT TAB)
External REFERENCE​
<strike></strike>
<strike></strike>
1235TypePrice
1236Comments
1237
TypePriceComments

<tbody>
</tbody>




1234 - has been fully filled out so the Lookup (renamed Report) should not return anything

1235 - In Data Tab all fields except "Type" and "Price" have been filled out. On Report Tab (previously named lookup) it needs to highlight what the missing data is by naming the column header. So return result is TYPE and PRICE. All other fields in the table have been filled in, so should NOT appear on the Report Tab.

1236 - Only empty field in Data Tab is comments, so the results should be the column header COMMENTS

1237 - 3 empty cells for reference 1237, Type, Price, Comments. This is the text I need to appear in REPORT tab



Hope that makes more sense!




This down here, was what you asked for:


Do you need something different?
Then you could explain again what you have, where you have it, and what you expect from the result.

Please Note
-----------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).
(DATA TAB)
Internal Ref External Ref DETAILS DATE REC'D REQUESTOR AREA PRIORITY DEVELOPER STATUS STATUS2 STATUS DATE ANALYST Type Price Comments
1
1234 Request 101/06/2019 Adam1 ASteveStage 1 A03/06/2019Sophie2500 On Target
21235 Request 202/06/2019 Peter1 BPhilStage 1 B03/06/2019Ellie On Target
31236 Request 3 03/06/2019 Andy1 ATomStage 3 C05/06/2019 Kaitlyn6050
41237 Request 403/06/2019 Katie1 CStuartStage 4 B08/06/2019 Hannah

<tbody>
</tbody>

(Before Macro is Run This Table is completely empty of any data)
(REPORT TAB)


<tbody style="border-collapse: collapse; width: auto;">
</tbody>


MACRO IS NOW RUN.



(REPORT TAB)
External REFERENCE​
<strike></strike>
<strike></strike>
1235TypePrice
1236Comments
1237
TypePriceComments

<tbody>
</tbody>




1234 - has been fully filled out so the Lookup (renamed Report) should not return anything

1235 - In Data Tab all fields except "Type" and "Price" have been filled out. On Report Tab (previously named lookup) it needs to highlight what the missing data is by naming the column header. So return result is TYPE and PRICE. All other fields in the table have been filled in, so should NOT appear on the Report Tab.

1236 - Only empty field in Data Tab is comments, so the results should be the column header COMMENTS

1237 - 3 empty cells for reference 1237, Type, Price, Comments. This is the text I need to appear in REPORT tab



Hope that makes more sense!




This down here, was what you asked for:


Do you need something different?
Then you could explain again what you have, where you have it, and what you expect from the result.

Please Note
-----------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,102
Office Version
2007
Platform
Windows
1234 - has been fully filled out so the Lookup (renamed Report) should not return anything

1235 - In Data Tab all fields except "Type" and "Price" have been filled out. On Report Tab (previously named lookup) it needs to highlight what the missing data is by naming the column header. So return result is TYPE and PRICE. All other fields in the table have been filled in, so should NOT appear on the Report Tab.

1236 - Only empty field in Data Tab is comments, so the results should be the column header COMMENTS

1237 - 3 empty cells for reference 1237, Type, Price, Comments. This is the text I need to appear in REPORT tab
Try this

Code:
Sub Create_Report()
    Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
    Dim k As Long, lr As Long
    
    Application.ScreenUpdating = False
    Set sh1 = Sheets("[COLOR=#ff0000]Data[/COLOR]")
    Set sh2 = Sheets("[COLOR=#ff0000]LOOK UP[/COLOR]")
    sh2.Cells.ClearContents
    For Each c In sh1.Range("B2", sh1.Range("B" & Rows.Count).End(xlUp))
        lr = sh2.Range("A" & Rows.Count).End(xlUp).Row + 1
        k = 2
        For j = Columns("C").Column To sh1.Cells(1, Columns.Count).End(xlToLeft).Column
            If sh1.Cells(c.Row, j).Value = "" Then
                sh2.Cells(lr, "A").Value = c.Value
                sh2.Cells(lr, k).Value = sh1.Cells(1, j).Value
                k = k + 1
            End If
        Next
    Next
    Application.ScreenUpdating = True
    MsgBox "End"
End Sub
 

surkdidat

Active Member
Joined
Oct 1, 2011
Messages
359
Excellent - that does the job, just I need it to only do Columns A through to K, and Columns R, S, T, U, V, W, X, Y, X, AA, AB, AE, AF, AK. AL. AM. AN

Is it (as an afterthought) possible to ALWAYS include the contents on Column L where the below applies please? (sorry!)


Try this

Code:
Sub Create_Report()
    Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
    Dim k As Long, lr As Long
    
    Application.ScreenUpdating = False
    Set sh1 = Sheets("[COLOR=#ff0000]Data[/COLOR]")
    Set sh2 = Sheets("[COLOR=#ff0000]LOOK UP[/COLOR]")
    sh2.Cells.ClearContents
    For Each c In sh1.Range("B2", sh1.Range("B" & Rows.Count).End(xlUp))
        lr = sh2.Range("A" & Rows.Count).End(xlUp).Row + 1
        k = 2
        For j = Columns("C").Column To sh1.Cells(1, Columns.Count).End(xlToLeft).Column
            If sh1.Cells(c.Row, j).Value = "" Then
                sh2.Cells(lr, "A").Value = c.Value
                sh2.Cells(lr, k).Value = sh1.Cells(1, j).Value
                k = k + 1
            End If
        Next
    Next
    Application.ScreenUpdating = True
    MsgBox "End"
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,102
Office Version
2007
Platform
Windows
Excellent - that does the job, just I need it to only do Columns A through to K, and Columns R, S, T, U, V, W, X, Y, X, AA, AB, AE, AF, AK. AL. AM. AN
please? (sorry!)

That's why my insistence that you include in your explanation what you need.
And that was not in your explanation, that's why the macro does not do it.
You could explain again, with complete examples.


And finally, this part I do not understand.
Is it (as an afterthought) possible to ALWAYS include the contents on Column L where the below applies
If it is a requirement, you must explain it with examples.
 

surkdidat

Active Member
Joined
Oct 1, 2011
Messages
359
Hi DanteAmor

Firstly, thank you for all your help so far. The code is running "perfectly", ie is is bringing up the column headers for anything that is empty.

So for the columns listed (Columns A through to L, and then any blank cells from Column R, S, T, U, V, W, X, Y, X, AA, AB, AE, AF, AK. AL. AM. AN) - these need to be included and all other columns ignored


So the code to do exactly the same but only for Columns A, C, D, E, F, G, H, I, J, K, L, R, S, T, U, V, W, X, Y, AA, AB, AE, AF, AK, AL, AM, AN

If the column M, N, O, P, Q Z, AC, AD, AG, AH, AI, AJ are blank/contain text I need the VBA to ignore these.

With Column L to have the contents displayed alongside the reference number (this is the name of who is dealing with it) so makes it easier to deal with

So on the below I have Column L as the Analyst, this on, any information the Macro generates, I would like to include next to the reference number.

On the below data this goes from A-L, and then restarts at Column R. Any information (for this report based in
M, N, O, P, Q Z, AC, AD, AG, AH, AI, AJ are completely irrelevant, regardless of any content or not, and should be ignored by this please.

Apologies again, I posted it in my initial post, and then when trying to explain again, I forgot to include it in my requirements!!


(DATA TAB)
ABCDEFGHIJKLRST
Internal Ref External Ref DETAILS DATE REC'D REQUESTOR AREA PRIORITY DEVELOPER STATUS STATUS2 STATUS DATE ANALYST Type Price Comments
1 1234 Request 101/06/2019 Adam1 ASteveStage 1 A03/06/2019Sophie2500 On Target
21235 Request 202/06/2019 Peter1 BPhilStage 1 B03/06/2019Ellie On Target
31236 Request 3 03/06/2019 Andy1 ATomStage 3 C05/06/2019 Kaitlyn6050
41237 Request 403/06/2019 Katie1 CStuartStage 4 B08/06/2019 Hannah

<tbody>
</tbody>









That's why my insistence that you include in your explanation what you need.
And that was not in your explanation, that's why the macro does not do it.
You could explain again, with complete examples.


And finally, this part I do not understand.


If it is a requirement, you must explain it with examples.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,102
Office Version
2007
Platform
Windows
Hi DanteAmor

Firstly, thank you for all your help so far. The code is running "perfectly", ie is is bringing up the column headers for anything that is empty.

So for the columns listed (Columns A through to L, and then any blank cells from Column R, S, T, U, V, W, X, Y, X, AA, AB, AE, AF, AK. AL. AM. AN) - these need to be included and all other columns ignored


So the code to do exactly the same but only for Columns A, C, D, E, F, G, H, I, J, K, L, R, S, T, U, V, W, X, Y, AA, AB, AE, AF, AK, AL, AM, AN

If the column M, N, O, P, Q Z, AC, AD, AG, AH, AI, AJ are blank/contain text I need the VBA to ignore these.

With Column L to have the contents displayed alongside the reference number (this is the name of who is dealing with it) so makes it easier to deal with

So on the below I have Column L as the Analyst, this on, any information the Macro generates, I would like to include next to the reference number.

On the below data this goes from A-L, and then restarts at Column R. Any information (for this report based in
M, N, O, P, Q Z, AC, AD, AG, AH, AI, AJ are completely irrelevant, regardless of any content or not, and should be ignored by this please.

Apologies again, I posted it in my initial post, and then when trying to explain again, I forgot to include it in my requirements!!


(DATA TAB)
ABCDEFGHIJKLRST
Internal Ref External Ref DETAILS DATE REC'D REQUESTOR AREA PRIORITY DEVELOPER STATUS STATUS2 STATUS DATE ANALYST Type Price Comments
1 1234 Request 101/06/2019 Adam1 ASteveStage 1 A03/06/2019Sophie2500 On Target
21235 Request 202/06/2019 Peter1 BPhilStage 1 B03/06/2019Ellie On Target
31236 Request 3 03/06/2019 Andy1 ATomStage 3 C05/06/2019 Kaitlyn6050
41237 Request 403/06/2019 Katie1 CStuartStage 4 B08/06/2019 Hannah

<tbody>
</tbody>

Try this

Code:
Sub Create_Report()
    Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
    Dim k As Long, lr As Long, cols As Variant, cl As Variant
    
    Application.ScreenUpdating = False
    Set sh1 = Sheets("Data")
    Set sh2 = Sheets("LOOK UP")
    sh2.Cells.ClearContents
    
    cols = Array("A", "C", "D", "E", "f", "G", "H", "i", "J", "k", "L", "R", "S", "T", _
                 "U", "V", "W", "X", "Y", "AA", "AB", "AE", "AF", "AK", "AL", "AM", "AN")
    
    For Each c In sh1.Range("B2", sh1.Range("B" & Rows.Count).End(xlUp))
        lr = sh2.Range("A" & Rows.Count).End(xlUp).Row + 1
        k = 2
        For Each cl In cols
            If sh1.Cells(c.Row, cl).Value = "" Then
                sh2.Cells(lr, "A").Value = c.Value
                sh2.Cells(lr, k).Value = sh1.Cells(1, cl).Value
                k = k + 1
            End If
        Next
    Next
    Application.ScreenUpdating = True
    MsgBox "End"
End Sub
I still do not understand this part.
Please put examples.
So on the below I have Column L as the Analyst, this on, any information the Macro generates, I would like to include next to the reference number.
 
Last edited:

surkdidat

Active Member
Joined
Oct 1, 2011
Messages
359
REPORT TAB)

External REFERENCE
External Reference
Analyst
(Blank 1)(Blank 2)(Blank 3)
1235EllieTypePrice
1236KaitlynComments
1237HannahTypePriceComments

<tbody>
</tbody>


The Analyst Data is in column L in the DATA Table. So for anything that would appear in the table normally, the Analyst needs to appear alongside the reference number (this is a mandatory field so will always be in the data tab)




Try this

Code:
Sub Create_Report()
    Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
    Dim k As Long, lr As Long, cols As Variant, cl As Variant
    
    Application.ScreenUpdating = False
    Set sh1 = Sheets("Data")
    Set sh2 = Sheets("LOOK UP")
    sh2.Cells.ClearContents
    
    cols = Array("A", "C", "D", "E", "f", "G", "H", "i", "J", "k", "L", "R", "S", "T", _
                 "U", "V", "W", "X", "Y", "AA", "AB", "AE", "AF", "AK", "AL", "AM", "AN")
    
    For Each c In sh1.Range("B2", sh1.Range("B" & Rows.Count).End(xlUp))
        lr = sh2.Range("A" & Rows.Count).End(xlUp).Row + 1
        k = 2
        For Each cl In cols
            If sh1.Cells(c.Row, cl).Value = "" Then
                sh2.Cells(lr, "A").Value = c.Value
                sh2.Cells(lr, k).Value = sh1.Cells(1, cl).Value
                k = k + 1
            End If
        Next
    Next
    Application.ScreenUpdating = True
    MsgBox "End"
End Sub
I still do not understand this part.
Please put examples.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,102
Office Version
2007
Platform
Windows
REPORT TAB)

External REFERENCE
External ReferenceAnalyst(Blank 1)(Blank 2)(Blank 3)
1235EllieTypePrice
1236KaitlynComments
1237HannahTypePriceComments

<tbody>
</tbody>


The Analyst Data is in column L in the DATA Table. So for anything that would appear in the table normally, the Analyst needs to appear alongside the reference number (this is a mandatory field so will always be in the data tab)

Use this please

Code:
Sub Create_Report()
    Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
    Dim k As Long, lr As Long, cols As Variant, cl As Variant
    
    Application.ScreenUpdating = False
    Set sh1 = Sheets("Data")
    Set sh2 = Sheets("LOOK UP")
    sh2.Cells.ClearContents
    
    cols = Array("A", "C", "D", "E", "f", "G", "H", "i", "J", "k", "L", "R", "S", "T", _
                 "U", "V", "W", "X", "Y", "AA", "AB", "AE", "AF", "AK", "AL", "AM", "AN")
    
    For Each c In sh1.Range("B2", sh1.Range("B" & Rows.Count).End(xlUp))
        lr = sh2.Range("A" & Rows.Count).End(xlUp).Row + 1
[COLOR=#0000ff]        k = 3[/COLOR]
        For Each cl In cols
            If sh1.Cells(c.Row, cl).Value = "" Then
                sh2.Cells(lr, "A").Value = c.Value
[COLOR=#0000ff]                sh2.Cells(lr, "B").Value = sh1.Cells(c.Row, "L")[/COLOR]
                sh2.Cells(lr, k).Value = sh1.Cells(1, cl).Value
                k = k + 1
            End If
        Next
    Next
    Application.ScreenUpdating = True
    MsgBox "End"
End Sub
 

surkdidat

Active Member
Joined
Oct 1, 2011
Messages
359
Thank you so much for all this and being very patient with me,- sorry for all the confusion. Ironically, it has helped me understand the code more though having seen certain bits run, and then the extra bits added!

Use this please

Code:
Sub Create_Report()
    Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
    Dim k As Long, lr As Long, cols As Variant, cl As Variant
    
    Application.ScreenUpdating = False
    Set sh1 = Sheets("Data")
    Set sh2 = Sheets("LOOK UP")
    sh2.Cells.ClearContents
    
    cols = Array("A", "C", "D", "E", "f", "G", "H", "i", "J", "k", "L", "R", "S", "T", _
                 "U", "V", "W", "X", "Y", "AA", "AB", "AE", "AF", "AK", "AL", "AM", "AN")
    
    For Each c In sh1.Range("B2", sh1.Range("B" & Rows.Count).End(xlUp))
        lr = sh2.Range("A" & Rows.Count).End(xlUp).Row + 1
[COLOR=#0000ff]        k = 3[/COLOR]
        For Each cl In cols
            If sh1.Cells(c.Row, cl).Value = "" Then
                sh2.Cells(lr, "A").Value = c.Value
[COLOR=#0000ff]                sh2.Cells(lr, "B").Value = sh1.Cells(c.Row, "L")[/COLOR]
                sh2.Cells(lr, k).Value = sh1.Cells(1, cl).Value
                k = k + 1
            End If
        Next
    Next
    Application.ScreenUpdating = True
    MsgBox "End"
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,102
Office Version
2007
Platform
Windows
Thank you so much for all this and being very patient with me,- sorry for all the confusion. Ironically, it has helped me understand the code more though having seen certain bits run, and then the extra bits added!
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,101,956
Messages
5,483,883
Members
407,419
Latest member
Napoleao Paca

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top