Moving vertical entries to a horizontal entries and eliminate the extra space

[RpTheHotrod]

First, thanks for continuing to be an awesome community!

Second, I'm trying to accomplish something by forumla. I'm not familiar with VBA in excel, but if I need to do that, then I'm willing to work my way through it.

Here's an image of my current challenge:

https://i.imgur.com/MNyjiwb.png

Essentially, the top part of the image is what I have currently (Originally I only had ABC, but I needed to make the dates a header, and I could then populate the NUMBER accordingly). However, I need to now "save" space by collapsing an ID to only one row. The results of this would be the AFTER part at the bottom of the image.

I thought about using VLOOKUP, but VLOOKUP would end up searching for data outside of each ID section. For example, if I did a VLOOKUP in column DATE, I'd be searching for dates in both NAME(ID) and NAME 2(ID). The dates are not always the same for every ID.

Any ideas?

 

[Rick Rothstein]

Give this macro a try (it places its output at the bottom of your data after the last name's data group)...

[table="width: 500"]
[tr]
	[td]Sub RearrangeAndCollapse()
  Dim LastRow As Long, LastCol As Long, NameBlocks As Range, Ar As Range
  LastRow = Cells(Rows.Count, "B").End(xlUp).Row
  LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
  Set NameBlocks = Range("B2", Cells(Rows.Count, "B").End(xlUp)).SpecialCells(xlConstants)
  Application.ScreenUpdating = False
  Cells(LastRow + 2, "A").Resize(, LastCol - 2) = Split("Name," & Join(Application.Index(Range("D1", Cells(1, LastCol)).Value, 1, 0), ","), ",")
  On Error Resume Next
  For Each Ar In NameBlocks.Areas
    With Cells(Rows.Count, "A").End(xlUp)
      Ar(1).Offset(, -1).Copy .Offset(1)
      Intersect(Ar.EntireRow, Columns("D").Resize(, LastCol)).Copy .Offset(1, 1)
      .Offset(1, 1).Resize(Ar.Rows.Count, LastCol - 2).SpecialCells(xlBlanks).Delete xlShiftUp
    End With
  Next
  On Error GoTo 0
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (RearrangeAndCollapse) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[RpTheHotrod]

Give this macro a try (it places its output at the bottom of your data after the last name's data group)...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub RearrangeAndCollapse()
  Dim LastRow As Long, LastCol As Long, NameBlocks As Range, Ar As Range
  LastRow = Cells(Rows.Count, "B").End(xlUp).Row
  LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
  Set NameBlocks = Range("B2", Cells(Rows.Count, "B").End(xlUp)).SpecialCells(xlConstants)
  Application.ScreenUpdating = False
  Cells(LastRow + 2, "A").Resize(, LastCol - 2) = Split("Name," & Join(Application.Index(Range("D1", Cells(1, LastCol)).Value, 1, 0), ","), ",")
  On Error Resume Next
  For Each Ar In NameBlocks.Areas
    With Cells(Rows.Count, "A").End(xlUp)
      Ar(1).Offset(, -1).Copy .Offset(1)
      Intersect(Ar.EntireRow, Columns("D").Resize(, LastCol)).Copy .Offset(1, 1)
      .Offset(1, 1).Resize(Ar.Rows.Count, LastCol - 2).SpecialCells(xlBlanks).Delete xlShiftUp
    End With
  Next
  On Error GoTo 0
  Application.ScreenUpdating = True
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (RearrangeAndCollapse) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

This worked perfectly. Thank you! Now I get to dissect and figure out how it works :x