Automate Excel to shift data so the values in two rows match?
Solved/Closed
excellenthelp
excellenthelp
- Posts
- 4
- Registration date
- Friday June 27, 2014
- Status
- Member
- Last seen
- July 22, 2014
excellenthelp
- Posts
- 4
- Registration date
- Friday June 27, 2014
- Status
- Member
- Last seen
- July 22, 2014
Related:
- How to shift data up in excel
- How to shift data from one sheet to another in excel - Guide
- Excel data validation multiple values - Guide
- Excel snapshot of data - Guide
- Excel vba paste data below the last used row - Guide
- Excel vba append data from one sheet to another - Guide
3 replies
TrowaD
Jul 3, 2014 at 11:33 AM
- Posts
- 2886
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 27, 2022
Jul 3, 2014 at 11:33 AM
Hi Emily,
The code below will temporarily place the result in columns E and F. So make sure they are empty or else change the column references in the code.
Get back to us if further assistance is desired.
Best regards,
Trowa
The code below will temporarily place the result in columns E and F. So make sure they are empty or else change the column references in the code.
Get back to us if further assistance is desired.
Sub RunMe() Dim lRow, x As Long lRow = Range("C1").End(xlDown).Row For Each cell In Range("C2:C" & lRow) x = 2 Do If cell.Value = Cells(x, "B").Value Then Range(Cells(cell.Row, "C"), Cells(cell.Row, "D")).Copy Cells(x, "E").PasteSpecial End If x = x + 1 Loop Until Cells(x, "C") = vbNullString Next cell Range("E2:F" & lRow).Copy Range("C2").PasteSpecial Range("E2:F" & lRow).ClearContents Application.CutCopyMode = False End Sub
Best regards,
Trowa
excellenthelp
Jul 11, 2014 at 11:17 AM
- Posts
- 4
- Registration date
- Friday June 27, 2014
- Status
- Member
- Last seen
- July 22, 2014
Jul 11, 2014 at 11:17 AM
Hey Trowa!
Sorry it took a while for me to get back to you...I had to take your code and sort of "scale it up" to my actual spreadsheet. But it works great!! Thanks so much :)
I really appreciate the time and energy you put into this. You have saved us all a lot of headaches!
Thanks again,
Emily
Sorry it took a while for me to get back to you...I had to take your code and sort of "scale it up" to my actual spreadsheet. But it works great!! Thanks so much :)
I really appreciate the time and energy you put into this. You have saved us all a lot of headaches!
Thanks again,
Emily
excellenthelp
Jul 16, 2014 at 11:39 AM
- Posts
- 4
- Registration date
- Friday June 27, 2014
- Status
- Member
- Last seen
- July 22, 2014
Jul 16, 2014 at 11:39 AM
Hi Trowa!
The code you sent me works wonders on the sample spreadsheet I used for my example.
Unfortunately, when I run the code on my actual spreadsheet (which is obviously much longer, but same format), the code stops running when it hits a unique value in the C column that it can't match up with anything in the B column. Do you know any simple way to delete the cells in C and D when it can't find its "partner"??
Thanks again for all your help!
~Emily
The code you sent me works wonders on the sample spreadsheet I used for my example.
Unfortunately, when I run the code on my actual spreadsheet (which is obviously much longer, but same format), the code stops running when it hits a unique value in the C column that it can't match up with anything in the B column. Do you know any simple way to delete the cells in C and D when it can't find its "partner"??
Thanks again for all your help!
~Emily
TrowaD
Jul 17, 2014 at 10:56 AM
- Posts
- 2886
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 27, 2022
Jul 17, 2014 at 10:56 AM
Hi Emily,
But your example already had unique values in column C (2, 4 and 11) and they didn't show up at the result, right?
So what is the difference between your sample and actual data other then being a longer list?
If you don't mind you can upload your file (careful with personal info) using a file sharing site like www.speedyshare.com or ge.tt and then post back the download link.
Best regards,
Trowa
But your example already had unique values in column C (2, 4 and 11) and they didn't show up at the result, right?
So what is the difference between your sample and actual data other then being a longer list?
If you don't mind you can upload your file (careful with personal info) using a file sharing site like www.speedyshare.com or ge.tt and then post back the download link.
Best regards,
Trowa
excellenthelp
Jul 22, 2014 at 11:13 AM
- Posts
- 4
- Registration date
- Friday June 27, 2014
- Status
- Member
- Last seen
- July 22, 2014
Jul 22, 2014 at 11:13 AM
Hello Trowa-
I was able to figure it out this morning. Apparantely the spreadsheet I was using it on had some funny formatting, but now I got it! Thanks again for all your help!
~Emily
I was able to figure it out this morning. Apparantely the spreadsheet I was using it on had some funny formatting, but now I got it! Thanks again for all your help!
~Emily