Page 1 of 2 12 LastLast
Results 1 to 10 of 20

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

  1. #1
    Board Regular
    Join Date
    Oct 2011
    Posts
    337
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,343
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    14 Thread(s)

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

    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?
    Regards Dante Amor

  3. #3
    Board Regular
    Join Date
    Oct 2011
    Posts
    337
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

    Quote Originally Posted by DanteAmor View Post
    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?

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,343
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    14 Thread(s)

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

    Quote Originally Posted by surkdidat View Post
    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.
    Regards Dante Amor

  5. #5
    Board Regular
    Join Date
    Oct 2011
    Posts
    337
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    (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







    (LOOK UP TAB)
    External REFERENCE
    1235 Type Price
    1236 Comments
    1237 Type Price Comments

    Quote Originally Posted by DanteAmor View Post
    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 by surkdidat; Jun 18th, 2019 at 10:47 AM.

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,343
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    14 Thread(s)

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

    Quote Originally Posted by surkdidat View Post
    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("Data")
        Set sh2 = Sheets("LOOK UP")
        '
        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
    Regards Dante Amor

  7. #7
    Board Regular
    Join Date
    Oct 2011
    Posts
    337
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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
    Quote Originally Posted by DanteAmor View Post
    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("Data")
        Set sh2 = Sheets("LOOK UP")
        '
        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

  8. #8
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,343
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    14 Thread(s)

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

    Quote Originally Posted by surkdidat View Post
    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

    LOOK UP

     A
    1External REFERENCE
    21235
    31236
    41237
    Regards Dante Amor

  9. #9
    Board Regular
    Join Date
    Oct 2011
    Posts
    337
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.



    Quote Originally Posted by DanteAmor View Post
    You must have this on your "Lookup" sheet

    LOOK UP

    A
    1External REFERENCE
    21235
    31236
    41237

  10. #10
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,343
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    14 Thread(s)

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

    Quote Originally Posted by surkdidat View Post
    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).
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •