Sorting Every 2nd Row To Be On A Single Line

[Justijb]

Hello All,

New to the group so I apologize if I am not doing this correctly so any and all suggestions for a smooth intro/responses/conversation are welcomed.

I have a spreadsheet where every second row is a new entry/field with 5 columns of information.

Column ABCDE and Row 1& 2, correspond to the same data set. I want Row 2 and Column ABCDE to be moved up to Row 1, FGHIJ.

So every second row would apply through Row 27018.

Again, apologies if this is poorly explained but I can offer more info needed and thank you in advance.

R/

J

 

[JLGWhiz]

Try this on a copy first.

Sub t()
Dim sh As Worksheet, i As Long
Set sh = ActiveSheet
With sh
    For i = 2 To sh.Cells(Rows.Count, 1).End(xlUp).Row Step 2
        .Cells(i, 1).Resize(1, 5).Cut .Cells(i - 1, Columns.Count).End(xlToLeft).Offset(, 1)
    Next
    .Range("A1", .Cells(Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
End Sub

 

[Justijb]

JLGWhiz,

Thanks a million. This is exactly what I needed. I appreciate the quick response and support.

Look forward to offering advice when and where I can but also using this forum as a great tool of knowledge.

V/r

J

 

[JLGWhiz]

JLGWhiz,

Thanks a million. This is exactly what I needed. I appreciate the quick response and support.

Look forward to offering advice when and where I can but also using this forum as a great tool of knowledge.

V/r

J

Glad you could use it.
Regards, JLG

 

[Justijb]

Glad you could use it.
Regards, JLG

Hello all,

I may have jumped to conclusion. One final piece of the query. The code provided by JLGWhiz works perfectly fine but I suppose I needed to ask my question with even more specificity. And again, thanks for the reply.

Of each data set, every second row, the data in row 1 corresponds with row 2. e.g. A1 & A2 match. So when using the code provided it works perfect except for when the row doesn't have a matching set of information in the preceding row. Explanation: Row 1 through 67, the rows align, then row 68 it doesn't have a proceeding row that matches, then row 69&70 has a data set, then 71 does not.

So my question is in a sheet of 27,018 entries, is their a code to align the data to match by field a row 1 and row 2 and *also account for the fields in the rows that don't have corresponding data to match.

A
A
B
C
C
D
E
E

And make it look like

AA
B
CC
D
EE

Again, not sure if I'm explaining myself correctly so here's to hoping. As always, I can provide more information.

Thanks so much!

R/

J

 

[JLGWhiz]

Hello all,

I may have jumped to conclusion. One final piece of the query. The code provided by JLGWhiz works perfectly fine but I suppose I needed to ask my question with even more specificity. And again, thanks for the reply.

Of each data set, every second row, the data in row 1 corresponds with row 2. e.g. A1 & A2 match. So when using the code provided it works perfect except for when the row doesn't have a matching set of information in the preceding row. Explanation: Row 1 through 67, the rows align, then row 68 it doesn't have a proceeding row that matches, then row 69&70 has a data set, then 71 does not.

So my question is in a sheet of 27,018 entries, is their a code to align the data to match by field a row 1 and row 2 and *also account for the fields in the rows that don't have corresponding data to match.

A
A
B
C
C
D
E
E

And make it look like

AA
B
CC
D
EE

Again, not sure if I'm explaining myself correctly so here's to hoping. As always, I can provide more information.

Thanks so much!

R/

J

I would need to see a screen shot of your actual worksheet. I do not understand what you mean by 'matching rows'. What matches? Re-stated, what is the criteria that determines if the next row is a matching row or not?

 

[Justijb]

I would need to see a screen shot of your actual worksheet. I do not understand what you mean by 'matching rows'. What matches? Re-stated, what is the criteria that determines if the next row is a matching row or not?

Here is the table in question:

64| 967371758 56647552 N865MLB EC 06/30/2016
65| 967371758 56647552 AMASPROC OM 07/04/2016
66| 967372366 56647964 N887BAR EC 07/01/2016
67| 967372366 56647964 AMASPROC OM 07/04/2016
68| 967372416 56623139 AMASPROC OM 07/04/2016
69| 967372537 56648087 N865MLB EC 07/01/2016
70| 967372537 56648087 AMASPROC OM 07/04/2016
71| 967372617 55818224 AMASPROC OM 07/04/2016
72| 967372644 56648167 N813DR EC 07/01/2016
73| 967372644 56648167 AMASPROC OM 07/04/2016

So rows 64&65 would go on the same row 64 ABCDE FGHIJ, same with rows 66&67. Those are the matching rows throughout the sheet. The issue I'm running into is when there is a row that does not have a matching row as in Row 68 and Row 71. So when I ran the original code you gave me, it worked perfectly but it threw off the rest of the data sets when ever there was a row that didn't have a second matching data set.

Hopefully I can explained it a little better for you.

Thanks again for all your help JLGWhiz!

R/

J

 

[Rick Rothstein]

Here is the table in question:

64| 967371758 56647552 N865MLB EC 06/30/2016
65| 967371758 56647552 AMASPROC OM 07/04/2016
66| 967372366 56647964 N887BAR EC 07/01/2016
67| 967372366 56647964 AMASPROC OM 07/04/2016
68| 967372416 56623139 AMASPROC OM 07/04/2016
69| 967372537 56648087 N865MLB EC 07/01/2016
70| 967372537 56648087 AMASPROC OM 07/04/2016
71| 967372617 55818224 AMASPROC OM 07/04/2016
72| 967372644 56648167 N813DR EC 07/01/2016
73| 967372644 56648167 AMASPROC OM 07/04/2016

So rows 64&65 would go on the same row 64 ABCDE FGHIJ, same with rows 66&67. Those are the matching rows throughout the sheet. The issue I'm running into is when there is a row that does not have a matching row as in Row 68 and Row 71.

Does this do what you want...

[table="width: 500"]
[tr]
	[td]Sub MoveSecondMatchingItemsToFirstItems()
  Dim R As Long
  For R = 2 To Cells(Rows.Count, "A").End(xlUp).Row
    If Cells(R, "A").Value = Cells(R - 1, "A").Value Then
      Cells(R, 1).Resize(1, 5).Cut Cells(R - 1, Columns.Count).End(xlToLeft).Offset(, 1)
    End If
  Next
  Columns("A").SpecialCells(xlBlanks).EntireRow.Delete
End Sub[/td]
[/tr]
[/table]

 

[Justijb]

Does this do what you want...

[table="width: 500"]
[tr]
	[td]Sub MoveSecondMatchingItemsToFirstItems()
  Dim R As Long
  For R = 2 To Cells(Rows.Count, "A").End(xlUp).Row
    If Cells(R, "A").Value = Cells(R - 1, "A").Value Then
      Cells(R, 1).Resize(1, 5).Cut Cells(R - 1, Columns.Count).End(xlToLeft).Offset(, 1)
    End If
  Next
  Columns("A").SpecialCells(xlBlanks).EntireRow.Delete
End Sub[/td]
[/tr]
[/table]

Rick, JLGWhiz. Et al.

Appreciate the support and assistance. This is a reminder that these types of forums can be very helpful and friendly.

Thank you! The codes provided were helpful and the task at hand was correctly and efficiently completed. Have a great week!

V/r

J

 

[Justijb]

Rick, JLGWhiz,

Thank you so much for your assistance in the past. I'm hoping you can assist me again with this next question I have. It's a spreadsheet with multiple columns (A through N) and around 18k records. I need help writing the code to sort on column D and I would need to highlight yellow, every row that has only one name in it.

E.g.

Rows two and three have 'regina" and nothing else while rows 4-23 has "Elizabeth lastname" and other rows have three names in it.

Is there any way to codify this action by space between words. I'm thinking this is the only way since every name is unique and multiple rows have the same name sometimes?

If further information is needed, please let me know. Hopefully you can work your magic again.

Thanks.

V/r


JB