Moving data from a single column to get more row values to work with

Onebracketshort

New Member
Joined
Dec 19, 2019
Messages
37
Office Version
  1. 2010
Platform
  1. Windows
Have a series of data in one column I'm trying to make easier to work with.

Looking to move the date entry in A3 to B4
Looking to move the vehicle entry value in A2 to C4

Similarly looking to move date entry in A8 to B9
looking to move vehicle entry A7 to C9

These moves are to be repeated for several thousand rows in total.

The vehicle value in column A is category general.
The date entry in column A is category custom dd-mmm-yy
The time entry in column A is category custom hh:mm

XL2BB capture:

Book1
ABC
1DateVehicle
2Lorry
303-Aug-20
421:47
522:03
611:16
7Coach
804-Aug-20
921:35
1022:37
1123:43
12Tractor
1305-Aug-20
1419:15
1519:57
Sheet2 (2)


Thanks for taking a look and for any pointers or solutions
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Copy B2 and C2 down to copy data, extend range of vehicles F1-F3 if necessary.

to remove Rows 2,3 and 7,8 etc.
Copy and paste values in Cols B and C, copy D2 down to put a marker to remove Rows with a filter in Row 1


Book1
ABCDEF
1DateVehicleLorry
2Lorry  XCoach
303-Aug-20  XTractor
421:4703-Aug-20Lorry 
522:03   
611:16   
7Coach  X
804-Aug-20  X
921:3504-Aug-20Coach 
1022:37   
1123:43   
12Tractor  X
1305-Aug-20  X
1419:1505-Aug-20  
1519:57   
Sheet1
Cell Formulas
RangeFormula
B2:B15B2=IF(CELL("format",A1)="D1",A1,"")
C2,C14,C11,C8,C5C2=IFERROR(INDEX($F$1:$F$3,MATCH(#REF!,$F$1:$F$3,0)),"")
D2:D15D2=IF(CELL("format",A2)="D9","","X")
C15,C12:C13,C9:C10,C6:C7,C3:C4C3=IFERROR(INDEX($F$1:$F$3,MATCH(A1,$F$1:$F$3,0)),"")
 
Upvote 0
See if this macro does what you want. Test with a copy of your data.

VBA Code:
Sub rearrange()
  Dim c As Range
  
  Application.ScreenUpdating = False
  For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants, xlTextValues)
    c.Offset(2, 2).Value = c.Value
    c.Offset(1).Copy Destination:=c.Offset(2, 1)
    c.Resize(2).ClearContents
  Next c
  Application.ScreenUpdating = True
End Sub

Before:

Onebracketshort 1.xlsm
ABC
1DateVehicle
2Lorry
303-Aug-20
421:47
522:03
611:16
7Coach
804-Aug-20
921:35
1022:37
1123:43
12Tractor
1305-Aug-20
1419:15
1519:57
Sheet1



After:

Onebracketshort 1.xlsm
ABC
1DateVehicle
2
3
421:4703-Aug-20Lorry
522:03
611:16
7
8
921:3504-Aug-20Coach
1022:37
1123:43
12
13
1419:1505-Aug-20Tractor
1519:57
Sheet1
 
Upvote 0
Works great, this has saved me hours of work. Peter and Alan thanks to both of you.
 
Upvote 0
You're welcome, glad we could help.

BTW, if you are going with a formula approach, I think this much simpler one should also work for you.

Onebracketshort 1.xlsm
ABC
1DateVehicle
2Lorry
303-Aug-20
421:4703-Aug-20Lorry
522:03  
611:16  
7Coach  
804-Aug-20  
921:3504-Aug-20Coach
1022:37  
1123:43  
12Tractor  
1305-Aug-20  
1419:1505-Aug-20Tractor
1519:57  
16  
17  
Sheet3
Cell Formulas
RangeFormula
B4:B17B4=IF(ISTEXT(A2),A3,"")
C4:C17C4=IF(B4="","",A2)
 
Upvote 0
Forgot to mention with my formula suggestion, if you want to remove the original Date & Vehicle rows ..
- Copy/Paste Values columns B & C as suggested by Alan
- Filter on existing column A with this custom AutoFilter

1599299544513.png


Result is like below so select from row 2 down and delete those rows. Then unfilter.

Onebracketshort 1.xlsm
ABC
1DateVehicle
2Lorry
303-Aug-20
7Coach
804-Aug-20
12Tractor
1305-Aug-20
18
Sheet3
 
Upvote 0
Thanks for that follow up Peter. I ended up going with the macro.

I’ve discovered that the way the data was recorded and presented in the original raw data has meant multiple dates were recorded under a given vehicle type in column A.

So in column A I have some dates I would like to move over to column B.

Is there a simple adjustment so the macro can be reused to only move the loose dates one column to the right and one column down?

XL2BB capture:



loose dates.xlsx
ABC
1DateVehicle
2
3
414-Nov-19
521:56
620:20
719:35
8
909/11/2019
1020:25
1119:33
1219:04
Sheet1
 
Upvote 0
Can you show some bigger representative sample data and the expected results? In post 7 there are no vehicles, no results in columns B:C and very little data in column A so it is hard to tell what you want or what any variety in the original data might be like.
 
Upvote 0
Yes , of course. Have included a before and after. I’m looking to move 06-Aug-2020 in A16 to B17. Would like to be able to search for dates throughout column A and move them one column to the right and one row down.



Before:

Before.xlsx
ABC
1DateVehicle
2
3
421:4703-Aug-20Lorry
522:03
611:16
7
8
921:3504-Aug-20Coach
1022:37
1123:43
12
13
1419:1505-Aug-20Tractor
1519:57
1606-Aug-20
1719:04
1820:08
Sheet1




After:

After.xlsx
ABC
1DateVehicle
2
3
421:4703-Aug-20Lorry
522:03
611:16
7
8
921:3504-Aug-20Coach
1022:37
1123:43
12
13
1419:1505-Aug-20Tractor
1519:57
16
1719:0406-Aug-20
1820:08
Sheet1
 
Upvote 0
Is that really the 'Before' or does it start like this?

Onebracketshort 1.xlsm
ABC
1DateVehicle
2Lorry
303-Aug-20
421:47
522:03
611:16
7Coach
804-Aug-20
921:35
1022:37
1123:43
12Tractor
1305-Aug-20
1419:15
1519:57
1606-Aug-20
1719:04
1820:08
Sheet2
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

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