Need a macro to match a row of cells in Columns H, I and J in sheet 2 to H, I and J in sheet 3. If all 3 match correspondingly, then cut that row (D:P) from sheet 2 and insert cut cells below last row of data in sheet 3 for that category. Shift cells down. If no matches do nothing. Data is dynamic for both sheets. (Using Excel2016) Thanks.
Last edited by light; 10-01-2023 at 03:49 PM.
Re: Match 3 col in 2 sheets cut and paste
Confusing, partially no sense, you should better explain and update your profile …
Last edited by Marc L; 09-30-2023 at 03:00 PM.
See attached file. In sheet 2, first row, col H is "1760", col I is "A",Col J is "BON". Macro needs to look in sheet 3 for similar which can be found in row 113 to row 119. So in row 120 I would want to paste the data since in fits in that category.
Re: Match 3 col in 2 sheets cut and paste
First update your profile, without I may can not help or I may not use any efficient fast way …
Originally Posted by light
Until here that's crystal clear.
But after you lost me :
What ?‼As Sheet2 does not have any header and Sheet3 does not have a 'Category' header !
So what that could mean ? Important information or totally confusing useless ?!
as you are not able to write a VBA procedure do not even try to explain how the code must work !
As a reminder it could be achieved just using Excel basics features like
any Excel beginner operating manually, so with the help of the Macro Recorder …
'Dynamic' does not exist under Excel, all is static when you launch a VBA procedure,
so useless or you could mean another thing than 'dynamic' ? …
I updated. By "Category" I mean the sections that are separated by a space.
Macro code:
Try on a copy of your file.
Please Login or Register to view this content.
Last edited by kvsrinivasamurthy; 09-30-2023 at 06:57 PM.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Thanks kvsrinivasamurthy
Pretty good, except there is an area where the data gets put into the wrong section. I highlighted
the data on sheet 2 in red so I could see where it goes in sheet 3. See Rows 28-35 and rows 67-75 in sheet 3 in updated
file sample3 for the problem. The majority was put in the correct areas.
Re: Match 3 col in 2 sheets cut and paste
According to your initial post attachment, Sheet2 rows #33 & 34 column J : is it an error ?
If yes then once you have posted a new attachment without any error we can give it a try for a Cut by block and
if not then we should go for a slower way with a Cut row by row …
Marc
You are correct about sht2, if it was supposed to be organized like sheet3. To clarify, sheet2 is my data input sheet.
Sheet3 is the organization of that data.
Sht2 will get hundreds of rows of data in real life and they are not neatly
organized at all as shown nor are there spaces. I did that manually and maybe threw you guys off. Sheet 3 is supposed
to have spaces to separate the sections or categories as I call them because they will grow as I input data. This is just
a small sample. If it would be easier to code sheet2 without spaces to transfer its data to sheet3 one row at a time that
would work for me.
Try this !
Ok, as your Sheet3 attachment has errors as well so will work as expected only with a correct worksheet,
too easy to say the code does not work when the original 'Category' data is not correct !
According to your initial post static attachment as it is so without any useless VBA module
an Excel basics block copy VBA demonstration for starters to paste only to Sheet3 worksheet module (v2) :
PHP Code:Sub Demo1()
Dim L&, R&, Rf as Range
Application.ScreenUpdating = False
With Range("C2", Cells(Rows.Count, 4).End(xlUp)(1, 0))
.Value2 = Evaluate(Replace("H2:H#&I2:I#&J2:J#", "#", .Rows.Count + 1))
End With
With Sheet2.Range("D1", Sheet2.Cells(Rows.Count, 4).End(xlUp))
.Resize(, 13).Sort .Cells(1, 5), 1, .Cells(1, 6), , 1, .Cells(1, 7), 1, 2
End With
With Sheet2.Range("C1", Sheet2.[D1].End(xlDown)(1, 0)).Rows
.Value2 = .Parent.Evaluate(Replace("H1:H#&I1:I#&J1:J#", "#", .Count))
For L = 1 To .Count
R = L
While .Cells(L + 1) = .Cells(R): L = L + 1: Wend
Set Rf = Columns(3).Find(.Cells(R), , xlValues, 1, , 2)
If Not Rf Is Nothing Then
Rows(Rf.Row)(2).Resize(L - R + 1).Insert
With .Cells(R).Resize(L - R + 1, 14): .Copy Rf(2): .Clear: End With
End If
Next
Set Rf = Nothing
.Resize(, 14).Sort .Cells(1), 1, Header:=2
End With
Application.ScreenUpdating = True
End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
Last edited by Marc L; 10-01-2023 at 03:01 PM. Reason: speed optimization ...
Excellent! Thanks Marc L.
Try
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks