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

surkdidat

Well-known Member
Joined
Oct 1, 2011
Messages
579
Office Version
  1. 2016
Is there anyway, if there is a value in a row in Column B, on a separate sheet to create a report (or whatever) that will display 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

Many thanks
 
(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).
 
Upvote 0

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

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