Search duplicate rows based on 3 columns in all worksheets and update

[PritishS]

Dear Sir/Madam,

Hope you are doing well!

I'm stuck with a new kind a problem.
Details:
I have a worksheet name 'MAT', which is basically a bill of material having columns 'Description', 'Make', 'CatNo' and 'Price'.

Description make CatNo Price
Pencil1 ABC PEN1 100
Pencil2 WER PEN2 123
Ruler1 QAS RUL1 50

Also I have 2 worksheet before 'MAT' named 'Pencil' and 'Ruler', containing database of all pencils and rulers. Those Sheets also have same column like 'Description', 'Make', 'CatNo' and 'Price'. These tables Price I update in regular interval.

Table: Pencil

Description make CatNo Price
Pencil1 ABC PEN1 105
Pencil2 WER PEN2 145
Pencil3 FCD PEN3 121
Pencil4 FCE PEN4 111

Table: Ruler
Description make CatNo Price
Ruler1 QAS RUL1 58
Ruler2 WER RUL2 55
Ruler3 FCD RUL3 32
Ruler4 FCE RUL4 15

Now I need a program, which will Start checking 'MAT' sheet form Row having Pencil1 and check in all two sheets and if found same component 'Pencil1' & 'ABC' & 'PEN1' in any sheet, it will print the price from that sheet to 'MAT' sheet in front of Pencil1 in 'New Price' column.

It's like in 'MAT' sheet

Description make CatNo Price New Price
Pencil1 ABC PEN1 100 105
Pencil2 WER PEN2 123 145
Ruler1 QAS RUL1 50 58

Now here what I have tired and got partial success:

Sub updateprice()

    Dim intRow1 As Integer
    Dim intRow2 As Integer
    Dim strNameSurname1 As String
    Dim strNameSurname2 As String
    
    intRow1 = 7 'The first row the data starts
    intRow2 = intRow1 + 1

    With Worksheets("BOM")
        Do While .Cells(intRow1, 1).Value <> Empty
            Do While .Cells(intRow2, 1).Value <> Empty
                strNameSurname1 = CStr(.Cells(intRow1, 1).Value) & CStr(.Cells(intRow1, 2).Value) & CStr(.Cells(intRow1, 3).Value)
                strNameSurname2 = CStr(.Cells(intRow2, 1).Value) & CStr(.Cells(intRow2, 2).Value) & CStr(.Cells(intRow2, 3).Value)
                If strNameSurname1 = strNameSurname2 Then
                    .Cells(intRow1, 5).Value = .Cells(intRow2, 4).Value '.Cells(intRow1, 4).Value
                   
                End If
                intRow2 = intRow2 + 1
            Loop
            intRow1 = intRow1 + 1
            intRow2 = intRow1 + 1
        Loop
    End With
End Sub

It updates the price if only the same records available in MAT sheet. Like-

Description make CatNo Price New Price
Pencil1 ABC PEN1 100 105
Pencil2 WER PEN2 123 145
Ruler1 QAS RUL1 50 58
Pencil1 ABC PEN1 105
Pencil2 WER PEN2 145
Ruler1 QAS RUL1 58

Basically, this code starts from Row 7 , concatenate 'Pencil1'&'ABC'&'PEN1' and checks for rows down. If found same concatenated record in below row it takes the Price of duplicate row and updates price in col-'New Price' of first row.

But I want this searching to be start from first sheet to all sheets except 'MAT' and if price found then update to col-"New Price' then go for next row of 'Pencil2' and again search into all worksheet and do this operation till the last row of MAT Sheet.

Hope my requirement is clear. I'm using Excel 2007 and Windows7 laptop.

I'll be grateful for any help.

Thanks & Regards,
PritishS

 

[MickG]

Without having to go over all that's gone before, could you send me a small set of data and an example of the expected results, so I can run the code over it and alter as required.
Regrds Mick

 

[PritishS]

Thanks for your reply Sir, I'll make a sample file with data and code. Can I share the same here via onedrive or gdrive link? If yes then I'll upload the file and share the link here.
Thanks
PritishS

 

[MickG]

As long as its safe is data and not a Image and it works, give it a try !!!!

 

[PritishS]

Dear Sir,

Here is the link of my sample file

https://drive.google.com/open?id=1mq-SeJpv1NgBFCtokyYqoXCAElgb3ON1

My requirement is written in 'MAT' sheet. You can clear 'NEW PRICE' column and click on 'UPDATE PRICE' button to check how it is working.

Thank you in advance.

Reagrds
PritishS

 

[MickG]

Try this:-
The code adds "Not Available" to the blank cells, because they are blank which means the values have not been found in other sheets. !!
https://app.box.com/s/ne2tsjh240o3yi6x3tqmplruizbmuecz

 

[PritishS]

Thank you very MUCH Mick Sir! It's works as expected on first run. I'll test all my conditions and confirm.
You are simply great. Wish you a Good health always!!

Thanks & Regards,
PritishS