Move Data From one cell to another if.

marbles08

New Member
Joined
Jan 7, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
So we have a repetitive task each day where stats are prepared before pulling them into an access database.

DateRun NoDrop NoStore No:Store NameTime DueArr timestart unloadA delB delC delrtn'sdep time
12/10/2020​
121020201011210202010130
40​
Cushwash
08:55​
3​
1​
8​
12/10/2020​
121020201011210202010135
40​
Cushwash
08:55​
1​
12/10/2020​
121020201011210202010115
45​
Aughnacloy
06:05​
3​
12/10/2020​
121020201011210202010120
45​
Aughnacloy
06:05​
1​
12/10/2020​
121020201011210202010125
55​
Newtownbutler
07:25​
3​
12/10/2020​
121020201011210202010140
60​
Enniskillen
09:55​
3​
12/10/2020​
121020201011210202010145
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:

DateRun NoDrop NoStore No:Drop PointTime DueArr timestart unloadA delB delC delrtn'sdep time
12/10/2020​
121020201011210202010130
40​
Cushwash
08:55​
3​
1​
8​
12/10/2020​
121020201011210202010115
45​
Aughnacloy
06:05​
3​
1​
12/10/2020​
121020201011210202010125
55​
Newtownbutler
07:25​
3​
12/10/2020​
121020201011210202010140
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?
 

Some videos you may like

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
Joined
Oct 27, 2005
Messages
19,993
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
48,345
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
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)),"")
 

Watch MrExcel Video

Forum statistics

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

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
Top