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

[surkdidat]

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

 

[DanteAmor]

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?

 

[surkdidat]

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?

 

[DanteAmor]

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.

 

[surkdidat]

(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 1

01/06/2019

Adam

1

A

Steve

Stage 1

A

03/06/2019

Sophie

2

500

On Target

2

1235

Request 2

02/06/2019

Peter

1

B

Phil

Stage 1

B

03/06/2019

Ellie

On Target

3

1236

Request 3

03/06/2019

Andy

1

A

Tom

Stage 3

C

05/06/2019

Kaitlyn

60

50

4

1237

Request 4

03/06/2019

Katie

1

C

Stuart

Stage 4

B

08/06/2019

Hannah

<tbody>
</tbody>

(LOOK UP TAB)
External REFERENCE​ <strike></strike>	<strike></strike>
1235	Type	Price
1236	Comments
1237	Type	Price	Comments

<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.

 

[DanteAmor]

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.

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

 

[surkdidat]

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.

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

 

[DanteAmor]

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>

 

[surkdidat]

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>

 

[DanteAmor]

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).