# Move Data From one cell to another if.

#### marbles08

##### New Member
So we have a repetitive task each day where stats are prepared before pulling them into an access database.

 Date Run No Drop No Store No: Store Name Time Due Arr time start unload A del B del C del rtn's dep time 12/10/2020​ 12102020101 1210202010130 40​ Cushwash 08:55​ 3​ 1​ 8​ 12/10/2020​ 12102020101 1210202010135 40​ Cushwash 08:55​ 1​ 12/10/2020​ 12102020101 1210202010115 45​ Aughnacloy 06:05​ 3​ 12/10/2020​ 12102020101 1210202010120 45​ Aughnacloy 06:05​ 1​ 12/10/2020​ 12102020101 1210202010125 55​ Newtownbutler 07:25​ 3​ 12/10/2020​ 12102020101 1210202010140 60​ Enniskillen 09:55​ 3​ 12/10/2020​ 12102020101 1210202010145 60​ Enniskillen 09:55​ 1​

Basically when a drop point has item b being delivered it pulls across into two different rows so we manually move the value delivered up into the same row as the A and C and delete the original row the B del was on so it looks like the below:

 Date Run No Drop No Store No: Drop Point Time Due Arr time start unload A del B del C del rtn's dep time 12/10/2020​ 12102020101 1210202010130 40​ Cushwash 08:55​ 3​ 1​ 8​ 12/10/2020​ 12102020101 1210202010115 45​ Aughnacloy 06:05​ 3​ 1​ 12/10/2020​ 12102020101 1210202010125 55​ Newtownbutler 07:25​ 3​ 12/10/2020​ 12102020101 1210202010140 60​ Enniskillen 09:55​ 3​ 1​

Is there a way to automate this task for a full spreadsheet also considering that not all drops receive B parcels?

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### Michael M

##### Well-known Member
How about
VBA Code:
Sub MM1()
Dim r As Long
For r = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Cells(r, 5).Value = Cells(r - 1, 5).Value Then Rows(r).Delete
Next r
End Sub

#### Peter_SSs

##### MrExcel MVP, Moderator
Welcome to the MrExcel board!

I think it might be this that you are wanting?

VBA Code:
Sub Move_Up()
Dim r As Long

Application.ScreenUpdating = False
For r = Range("A" & Rows.Count).End(xlUp).Row To 3 Step -1
If Range("J" & r).Value <> "" Then
Range("J" & r - 1).Value = Range("J" & r).Value
Rows(r).Delete
r = r - 1
End If
Next r
Application.ScreenUpdating = True
End Sub

#### Dossfm0q

##### Banned User
what about
Sheet2

Cell Formulas
RangeFormula
A2:A5A2=IFERROR(INDEX(Sheet1!\$A\$2:\$A\$8,AGGREGATE(15,6,(ROW(Sheet1!\$E\$2:\$E\$35)-ROW(Sheet1!\$E\$2)+1)/(ISNA(MATCH(Sheet1!\$E\$2:\$E\$8,Sheet2!\$E\$1:E1,0))),1)),"")
B2:B5B2=IFERROR(INDEX(Sheet1!\$B\$2:\$B\$8,AGGREGATE(15,6,(ROW(Sheet1!\$E\$2:\$E\$35)-ROW(Sheet1!\$E\$2)+1)/(ISNA(MATCH(Sheet1!\$E\$2:\$E\$8,Sheet2!\$E\$1:E1,0))),1)),"")
C2:C5C2=IFERROR(INDEX(Sheet1!\$C\$2:\$C\$8,AGGREGATE(15,6,(ROW(Sheet1!\$E\$2:\$E\$35)-ROW(Sheet1!\$E\$2)+1)/(ISNA(MATCH(Sheet1!\$E\$2:\$E\$8,Sheet2!\$E\$1:E1,0))),1)),"")
D2:D5D2=IFERROR(INDEX(Sheet1!\$D\$2:\$D\$8,AGGREGATE(15,6,(ROW(Sheet1!\$E\$2:\$E\$35)-ROW(Sheet1!\$E\$2)+1)/(ISNA(MATCH(Sheet1!\$E\$2:\$E\$8,Sheet2!\$E\$1:E1,0))),1)),"")
E2:E5E2=IFERROR(INDEX(Sheet1!\$E\$2:\$E\$8,AGGREGATE(15,6,(ROW(Sheet1!\$E\$2:\$E\$35)-ROW(Sheet1!\$E\$2)+1)/(ISNA(MATCH(Sheet1!\$E\$2:\$E\$8,Sheet2!\$E\$1:E1,0))),1)),"")
F2:F5F2=IFERROR(INDEX(Sheet1!\$F\$2:\$F\$8,AGGREGATE(15,6,(ROW(Sheet1!\$E\$2:\$E\$35)-ROW(Sheet1!\$E\$2)+1)/(ISNA(MATCH(Sheet1!\$E\$2:\$E\$8,Sheet2!\$E\$1:E1,0))),1)),"")
I2:I5I2=IFERROR(SUMPRODUCT(Sheet1!\$I\$2:\$I\$8*(--(Sheet1!\$E\$2:\$E\$8=E2)))/SUMPRODUCT((Sheet1!\$I\$2:\$I\$8<>"")*(Sheet1!\$E\$2:\$E\$8=E2)),"")
J2:J5J2=IFERROR(SUMPRODUCT(Sheet1!\$J\$2:\$J\$8*(--(Sheet1!\$E\$2:\$E\$8=E2)))/SUMPRODUCT((Sheet1!\$J\$2:\$J\$8<>"")*(Sheet1!\$E\$2:\$E\$8=E2)),"")
K2:K5K2=IFERROR(SUMPRODUCT(Sheet1!\$K\$2:\$K\$8*(--(Sheet1!\$E\$2:\$E\$8=E2)))/SUMPRODUCT((Sheet1!\$K\$2:\$K\$8<>"")*(Sheet1!\$E\$2:\$E\$8=E2)),"")

Replies
0
Views
84
Replies
3
Views
166
Replies
33
Views
680
Replies
2
Views
87
Replies
4
Views
100

Threads
1,126,888
Messages
5,621,434
Members
415,839
Latest member
faraji

### 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

### 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