Conditional Transpose/Match/Array Column to Rows

M

Mary7313

New Member
Joined
Feb 27, 2014
Messages
2
I've been trying to work out my problem for three days now. I've tried using a Pivot Table, a Match Array V-Look Up formula I found on here (and can't seem to find anymore) that I altered to fit my cell needs, and even implemented RP-Excel. Checked every Excel Forum I knew, and new ones I've never heard of before this problem. Googled and WikiHowed my brains out. Found lots of things that were similar but not quite what I needed. Also, asked the most Excel Savvy person here at work and didn't cover any new ground. Still can't seem to figure out how to do what I need. Any help would be greatly appreciated!

This is an example of what I have:

[TABLE="class: outer_border, width: 100"]
<tbody>[TR]
[TD][TABLE="width: 64"]
<tbody>[TR="class: outer_border"]
[TD="width: 64"]Box[/TD]
[/TR]
[TR="class: outer_border"]
[TD]101[/TD]
[/TR]
[TR="class: outer_border"]
[TD]101[/TD]
[/TR]
[TR="class: outer_border"]
[TD]102[/TD]
[/TR]
[TR="class: outer_border"]
[TD]102[/TD]
[/TR]
[TR="class: outer_border"]
[TD]102[/TD]
[/TR]
[TR="class: outer_border"]
[TD]102[/TD]
[/TR]
[TR="class: outer_border"]
[TD]103[/TD]
[/TR]
[TR="class: outer_border"]
[TD]103[/TD]
[/TR]
[TR="class: outer_border"]
[TD]103[/TD]
[/TR]
[TR="class: outer_border"]
[TD]104[/TD]
[/TR]
[TR="class: outer_border"]
[TD]105[/TD]
[/TR]
[TR="class: outer_border"]
[TD]105[/TD]
[/TR]
[TR="class: outer_border"]
[TD]105[/TD]
[/TR]
[TR="class: outer_border"]
[TD]106[/TD]
[/TR]
[TR="class: outer_border"]
[TD]106[/TD]
[/TR]
[TR="class: outer_border"]
[TD]106[/TD]
[/TR]
[TR="class: outer_border"]
[TD]107[/TD]
[/TR]
[TR="class: outer_border"]
[TD]108[/TD]
[/TR]
[TR="class: outer_border"]
[TD]108[/TD]
[/TR]
[TR="class: outer_border"]
[TD]108[/TD]
[/TR]
[TR="class: outer_border"]
[TD]109[/TD]
[/TR]
[TR="class: outer_border"]
[TD]109[/TD]
[/TR]
[TR="class: outer_border"]
[TD]109[/TD]
[/TR]
[TR="class: outer_border"]
[TD]109[/TD]
[/TR]
[TR="class: outer_border"]
[TD]109[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR="class: outer_border"]
[TD]Date[/TD]
[/TR]
[TR="class: outer_border"]
[TD]8/31 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]10/8 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]10/6 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]3/4 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]9/12 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]11/2 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]8/15 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]11/7 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]4/11 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]12/5 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]10/6 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]12/3 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]12/19 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]11/21 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]8/14 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]2/3 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]11/7 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]10/5 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]2/24 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]12/31 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]11/25 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]9/19 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]12/24 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]4/15 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]2/24 0:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

This is an example of what I'm trying to do:

[TABLE="class: outer_border, width: 100"]
<tbody>[TR]
[TD][TABLE="width: 64"]
<tbody>[TR="class: outer_border"]
[TD="width: 64"]Box[/TD]
[/TR]
[TR="class: outer_border"]
[TD]101[/TD]
[/TR]
[TR="class: outer_border"]
[TD]102[/TD]
[/TR]
[TR="class: outer_border"]
[TD]103[/TD]
[/TR]
[TR="class: outer_border"]
[TD]104[/TD]
[/TR]
[TR="class: outer_border"]
[TD]105[/TD]
[/TR]
[TR="class: outer_border"]
[TD]106[/TD]
[/TR]
[TR="class: outer_border"]
[TD]107[/TD]
[/TR]
[TR="class: outer_border"]
[TD]108[/TD]
[/TR]
[TR="class: outer_border"]
[TD]109[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR="class: outer_border"]
[TD]Date[/TD]
[/TR]
[TR="class: outer_border"]
[TD]8/31 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]10/6 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]8/15 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]12/5 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]10/6 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]11/21 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]11/7 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]10/5 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]11/25 0:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR="class: outer_border"]
[TD="width: 64"]Date[/TD]
[/TR]
[TR="class: outer_border"]
[TD]10/8 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]3/4 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]11/7 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD][/TD]
[/TR]
[TR="class: outer_border"]
[TD]12/3 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]8/14 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD][/TD]
[/TR]
[TR="class: outer_border"]
[TD]2/24 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]9/19 0:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR="class: outer_border"]
[TD]Date[/TD]
[/TR]
[TR="class: outer_border"]
[TD][/TD]
[/TR]
[TR="class: outer_border"]
[TD]9/12 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]4/11 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD][/TD]
[/TR]
[TR="class: outer_border"]
[TD]12/19 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]2/3 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD][/TD]
[/TR]
[TR="class: outer_border"]
[TD]12/31 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD]12/24 0:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR="class: outer_border"]
[TD="width: 64"]Date[/TD]
[/TR]
[TR="class: outer_border"]
[TD][/TD]
[/TR]
[TR="class: outer_border"]
[TD]11/2 0:00[/TD]
[/TR]
[TR="class: outer_border"]
[TD][/TD]
[/TR]
[TR="class: outer_border"]
[TD][/TD]
[/TR]
[TR="class: outer_border"]
[TD][/TD]
[/TR]
[TR="class: outer_border"]
[TD][/TD]
[/TR]
[TR="class: outer_border"]
[TD][/TD]
[/TR]
[TR="class: outer_border"]
[TD][/TD]
[/TR]
[TR="class: outer_border"]
[TD]4/15 0:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR="class: outer_border"]
[TD="width: 64"]Date[/TD]
[/TR]
[TR="class: outer_border"]
[TD][/TD]
[/TR]
[TR="class: outer_border"]
[TD][/TD]
[/TR]
[TR="class: outer_border"]
[TD][/TD]
[/TR]
[TR="class: outer_border"]
[TD][/TD]
[/TR]
[TR="class: outer_border"]
[TD][/TD]
[/TR]
[TR="class: outer_border"]
[TD][/TD]
[/TR]
[TR="class: outer_border"]
[TD][/TD]
[/TR]
[TR="class: outer_border"]
[TD][/TD]
[/TR]
[TR="class: outer_border"]
[TD]2/24 0:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Additional challenges:
The actual workbook is over 4,000 rows long with a lot of various other information in neighboring columns.
The dates represent each time a specific box was filled. Some boxes only filled once, some up to 14 times.
I only want up to the first five fills.

Able to try any of the following, or combinations of:
Formulas, Pivot Tables, VBA, RP Excel

THANK YOU in advance to anyone that helps me figure out the puzzle.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
Assuming Box number is sorted and this table starts from A1 then you can try the following code:

Code: 
Sub CompactMe2()
    Dim lr As Long: lr = Range("A" & Rows.Count).End(xlUp).Row
    Dim startrow As Long: startrow = 1
    Dim endrow As Long: endrow = 1
    
    'Transpose
    For r = 1 To lr
        If Range("A" & r).Value = Range("A" & r + 1).Value Then
            endrow = endrow + 1
        ElseIf endrow - startrow > 0 Then
            Range("B" & startrow + 1 & ":B" & endrow).Copy
            Range("C" & startrow).PasteSpecial , Transpose:=True
            startrow = r + 1
            endrow = r + 1
        Else
            startrow = r + 1
            endrow = r + 1
        End If
    Next r
    
    'Delete
    For r = lr To 2 Step -1
        If Range("A" & r).Value = Range("A" & r - 1).Value Then
            Rows(r).Delete
        End If
    Next r
End Sub
 
Upvote 0
Iggydarsa,

This code is fantastic! It did exactly what I've been beating my head against the wall to do!
I greatly appreciate your time and genius!

Thankfully and Happily,
Mary


iggydarsa said:
Assuming Box number is sorted and this table starts from A1 then you can try the following code:

Code: 
Sub CompactMe2()
    Dim lr As Long: lr = Range("A" & Rows.Count).End(xlUp).Row
    Dim startrow As Long: startrow = 1
    Dim endrow As Long: endrow = 1
    
    'Transpose
    For r = 1 To lr
        If Range("A" & r).Value = Range("A" & r + 1).Value Then
            endrow = endrow + 1
        ElseIf endrow - startrow > 0 Then
            Range("B" & startrow + 1 & ":B" & endrow).Copy
            Range("C" & startrow).PasteSpecial , Transpose:=True
            startrow = r + 1
            endrow = r + 1
        Else
            startrow = r + 1
            endrow = r + 1
        End If
    Next r
    
    'Delete
    For r = lr To 2 Step -1
        If Range("A" & r).Value = Range("A" & r - 1).Value Then
            Rows(r).Delete
        End If
    Next r
End Sub
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Count entry by time
Replies
3
Views
168
admvlad
A
M
  • Solved
sumifs using date and time values
Replies
9
Views
244
Asbestos_Jen
Asbestos_Jen
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
247
dshtesy
D
M
  • Question Question
Pivot table not showing as Numbers
Replies
1
Views
269
shift-del
shift-del
A
  • Question Question
Get enddatetime if X no. of working hours is added to startdatetime
Replies
1
Views
430
myall_blues
myall_blues

Forum statistics

Threads
1,223,236
Messages
6,170,906
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top