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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
In other words, if in a row, for example row 8, from column R to column AN there is data, then hide row 8?

And what do you want, to pass all the visible rows to another sheet?
 
Upvote 0
Hi

Sorry, will try and explain again

In a separate tab, I need it to look up (run Macro etc) if there is any value in column B (which will be the reference number)
In this tab, if, for example row 8, column B was 1234, it would look on that row and populate with the column header of any blanks. So, id R T V W AB were blank, whatever the column headers of these would appear alongside the reference number.

If row 9, column B was 1235 but all the columns listed above were populated, then it should ignore this completely.

If column B is blank, then no action should happen
On the main data tab, nothing needs to happen.

In other words, if in a row, for example row 8, from column R to column AN there is data, then hide row 8?

And what do you want, to pass all the visible rows to another sheet?
 
Upvote 0
Hi

Sorry, will try and explain again

In a separate tab, I need it to look up (run Macro etc) if there is any value in column B (which will be the reference number)
In this tab, if, for example row 8, column B was 1234, it would look on that row and populate with the column header of any blanks. So, id R T V W AB were blank, whatever the column headers of these would appear alongside the reference number.

If row 9, column B was 1235 but all the columns listed above were populated, then it should ignore this completely.

If column B is blank, then no action should happen
On the main data tab, nothing needs to happen.

Now I understand less.
You can explain it with images with real data, actual sheet names, what you want to do with the information.
 
Upvote 0
(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>







(LOOK UP TAB)
External REFERENCE
<strike></strike>
<strike></strike>
1235TypePrice
1236Comments
1237TypePriceComments

<tbody>
</tbody>

Now I understand less.
You can explain it with images with real data, actual sheet names, what you want to do with the information.
 
Last edited:
Upvote 0
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


Try this macro.
Change data in red for your information.

Code:
Sub Create_Report()
    Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, f As Range
    Dim k As Long
    
    Application.ScreenUpdating = False
    Set sh1 = Sheets("[COLOR=#ff0000]Data[/COLOR]")
    Set sh2 = Sheets("[COLOR=#ff0000]LOOK UP"[/COLOR])
    '
    sh2.Range("B2", Cells(Rows.Count, Columns.Count)).ClearContents
    For Each c In sh2.Range("A2", sh2.Range("A" & Rows.Count).End(xlUp))
        k = Columns("N").Column
        Set f = sh1.Range("B:B").Find(c.Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not f Is Nothing Then
            sh2.Cells(c.Row, "B").Resize(1, 12).Value = sh1.Cells(f.Row, "A").Resize(1, 12).Value
            For j = Columns("R").Column To Columns("AN").Column
                If sh1.Cells(f.Row, j).Value = "" Then
                    sh2.Cells(c.Row, k).Value = sh1.Cells(1, j).Value
                    k = k + 1
                End If
            Next
        End If
    Next
    Application.ScreenUpdating = True
    MsgBox "End"
End Sub


The macro reads the reference of the "lookup" sheet and looks for it in the "data" sheet, if find, it copies the columns of the A-L of the "Data" sheet and pastes them in the "look up" sheet.
Then check each column of the "data" sheet and if any cell is empty, then copy the title of that column from the "data" sheet and paste it on the "look up" sheet
 
Upvote 0
Hi There

Thank you for this, although I am still confused.

All my data is in "DATA" tab.
The tab "LOOKUP" is where I want my results and is currently blank.

I have run the Macro and it just brings up a msgbox "END"

From above, I think what you are saying is missing what I want.

On "LOOKUP" it only should bring up a reference if there is a blank cell in that row. It then brings up the reference (from column B) and the row header for the blank cell.

Thank you
Try this macro.
Change data in red for your information.

Code:
Sub Create_Report()
    Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, f As Range
    Dim k As Long
    
    Application.ScreenUpdating = False
    Set sh1 = Sheets("[COLOR=#ff0000]Data[/COLOR]")
    Set sh2 = Sheets("[COLOR=#ff0000]LOOK UP"[/COLOR])
    '
    sh2.Range("B2", Cells(Rows.Count, Columns.Count)).ClearContents
    For Each c In sh2.Range("A2", sh2.Range("A" & Rows.Count).End(xlUp))
        k = Columns("N").Column
        Set f = sh1.Range("B:B").Find(c.Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not f Is Nothing Then
            sh2.Cells(c.Row, "B").Resize(1, 12).Value = sh1.Cells(f.Row, "A").Resize(1, 12).Value
            For j = Columns("R").Column To Columns("AN").Column
                If sh1.Cells(f.Row, j).Value = "" Then
                    sh2.Cells(c.Row, k).Value = sh1.Cells(1, j).Value
                    k = k + 1
                End If
            Next
        End If
    Next
    Application.ScreenUpdating = True
    MsgBox "End"
End Sub


The macro reads the reference of the "lookup" sheet and looks for it in the "data" sheet, if find, it copies the columns of the A-L of the "Data" sheet and pastes them in the "look up" sheet.
Then check each column of the "data" sheet and if any cell is empty, then copy the title of that column from the "data" sheet and paste it on the "look up" sheet
 
Upvote 0
Hi There

Thank you for this, although I am still confused.

All my data is in "DATA" tab.
The tab "LOOKUP" is where I want my results and is currently blank.

I have run the Macro and it just brings up a msgbox "END"

From above, I think what you are saying is missing what I want.

On "LOOKUP" it only should bring up a reference if there is a blank cell in that row. It then brings up the reference (from column B) and the row header for the blank cell.

Thank you

You must have this on your "Lookup" sheet

<b>LOOK UP</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:124.51px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >External REFERENCE</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">1235</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">1236</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">1237</td></tr></table>
 
Upvote 0
Ah. okay thank you. I have done that and it is populating data (running when in lookup tab) - however, it just seems to be a carbon copy of my data sheet, and copying text from cells that have text (and its the field text). Any empty fields on data sheet are just blanks on the lookup tab.



You must have this on your "Lookup" sheet

<b>LOOK UP</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:124.51px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >External REFERENCE</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">1235</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">1236</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">1237</td></tr></table>
 
Upvote 0
Ah. okay thank you. I have done that and it is populating data (running when in lookup tab) - however, it just seems to be a carbon copy of my data sheet, and copying text from cells that have text (and its the field text). Any empty fields on data sheet are just blanks on the lookup tab.

This down here, was what you asked for:
on a separate sheet to create a report (or whatever) that will display Columns A through to L

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

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

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