Some entries with 2 rows of data, some only have one. Take 1st row where there's only one, take 2nd row, where there's 2

[marenello99]

Hi this is my first post here so sorry for any probs! I have a table with rows containing names and associated data, that I need to VLOOKUP into another spreadsheet. Some names have just one row, in which case I need the info from that row, but others have two rows of data and in this case I only need the second row of data. I've tried to show this!

So for 'Person A' i'd need all the data in 'extra' row, for 'Person B' I'd need all the data in the '(Basic)' row as they have no '(Extra)' data and for 'Person C', I'd need all the data in the 'extra' row.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Person A (Basic)[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Person A (Extra)[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]Person B (Basic)[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Person C (Basic)[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Person C (Extra)[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[/TR]
</tbody>[/TABLE]

 

[JLGWhiz]

Can you use a macro?

Sub copystuff()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets(1) 'Edit sheet name - this is sheet with original data
Set sh2 = Sheets(2) 'Edit sheet name
    For Each c In sh1.Range("A2", sh1.Cells(Rows.Count, 1).End(xlUp))
        If c.Value = c.Offset(1, 0).Value Then
            GoTo SKIP:
        Else
            c.EntireRow.Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
        End If
SKIP:
    Next
End Sub

 

[marenello99]

Can you use a macro?

Sub copystuff()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets(1) 'Edit sheet name - this is sheet with original data
Set sh2 = Sheets(2) 'Edit sheet name
    For Each c In sh1.Range("A2", sh1.Cells(Rows.Count, 1).End(xlUp))
        If c.Value = c.Offset(1, 0).Value Then
            GoTo SKIP:
        Else
            c.EntireRow.Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
        End If
SKIP:
    Next
End Sub

------------------------------------------------

Great thanks for this, I'll give it a go, although my knowledge is very limited on macros!

 

[JLGWhiz]

------------------------------------------------

Great thanks for this, I'll give it a go, although my knowledge is very limited on macros!

To use the macro, copy it to your standard code module 1. To access that code module, press Alt + F11. If the large pane is dark when the VB editor opens, click 'Insert' on the editor menu bar, then click 'Module'. When the large pane is bright then you can copy the code into it. To run the code from the Excel window, press Alt + F8, click on the macro name in the Macro Dialog Box that appears, then click 'Run'. You can also use the Options button to assign a keyboard shortcut for the macro. If you will have continuing need for the macro, then save your workbook as a macro enabled workbook with file extension .xlsm.

Regards, JLG

 

[marenello99]

To use the macro, copy it to your standard code module 1. To access that code module, press Alt + F11. If the large pane is dark when the VB editor opens, click 'Insert' on the editor menu bar, then click 'Module'. When the large pane is bright then you can copy the code into it. To run the code from the Excel window, press Alt + F8, click on the macro name in the Macro Dialog Box that appears, then click 'Run'. You can also use the Options button to assign a keyboard shortcut for the macro. If you will have continuing need for the macro, then save your workbook as a macro enabled workbook with file extension .xlsm.

Regards, JLG

----------------------

Thanks JLG, I have clicked the 'like' option by your comment, but I can't see a 'helpful' button or anything on this forum, so guessing that's not the way it works on here, so I'll just have to say cheers like this!

 

[JLGWhiz]

----------------------

Thanks JLG, I have clicked the 'like' option by your comment, but I can't see a 'helpful' button or anything on this forum, so guessing that's not the way it works on here, so I'll just have to say cheers like this!

Thanks for the feedback,
Regards, JLG