Move Data From one cell to another if.

[marbles08]

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?

 

[Michael M]

How about

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]

Welcome to the MrExcel board!

I think it might be this that you are wanting?

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]

what about
Sheet2

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	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
2	12/10/2020	12102020101	1210202010130	40	Cushwash	08:55			3	1	8
3	12/10/2020	12102020101	1210202010115	45	Aughnacloy	06:05			3	1
4	12/10/2020	12102020101	1210202010125	55	Newtownbutler	07:25			3
5	12/10/2020	12102020101	1210202010140	60	Enniskillen	09:55			3	1
Sheet2

Cell Formulas
Range	Formula
A2:A5	A2	=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:B5	B2	=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:C5	C2	=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:D5	D2	=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:E5	E2	=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:F5	F2	=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:I5	I2	=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:J5	J2	=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:K5	K2	=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)),"")