Replace old content with new content base on Cell

[trekker1218]

Sheet 1

	A	B	C	D	E	F	G	H	I
1	Old	A/C Condensing Units Residential	BTU	Voltage Size	Heating
2	New	A/C Condensing Units Residential​	Cooling BTU	Voltage	Heating BTU
3	Old	A/C Unit Parts (OEM Only)	BTU	Heating	Amperage	Voltage Size
4	New	A/C Unit Parts (OEM Only)	AC COOLING BTU	Heating Area	AMPS	AC VOLTAGE
5

Sheet 2

	a	b	c	d	e	f	g	h	i
1	A/C Condensing Units Residential	BTU	Voltage Size	Heating
2	A/C Unit Parts (OEM Only)	BTU	Voltage Size	Heating
3

I need a VBA or other solution for this:
I need to match the category in sheet1 B1 to Sheet2 A1 Then replace the old data in Sheet2 C1 with the new data in Sheet1 C2. It has to match the Category in Sheet 2 ColA because the old and new data does not always match the columns between sheets. But the OLD and NEW data will always be rows 1 & 2 and 3 & 4 etc...
I have a sheet2 with 50K lines of data that I need to find and replace old data with new data.

Result like this would be great.

	a	b	c	d	e	f
1	A/C Condensing Units Residential	Cooling BTU	Voltage	Heating BTU
2	A/C Unit Parts (OEM Only)	AC COOLING BTU	Heating Area	AMPS	AC VOLTAGE
3
4

Even if it has to write an entire NEW sheet to move the data. That would work. I would just copy and paste the results to the file I need.

Thanks,
Tino

 

[mumps]

The reason columns N and P in sheet2 don't change is because the values in those columns do not correspond to the OLD values for Attributes 7 & and 8 in sheet1. There is another problem that I can see. You may recall I asked you (Posts 32 and 33) if the attribute values in sheet2 for any Sub Category will always be the same for all the items in that category and you responded that they were all the same. However, in sheet2 I can see that in most columns the attributes for any category are not the same. It's hard to get a working solution when the parameters keep changing.

 

[trekker1218]

Hi,
I do apologize. I will have all the attributes sorted to show in same column like the NEW ATTACHED file Here:

TEST-FILE-sorted.xlsx

Shared with Dropbox

www.dropbox.com

The attributes will be the same PER Category however the attribute can be in different columns based on that category.
for instance. VOLTAGE Rating in in COL P for 1st catagory but is in COL N for NEXT...

 

[mumps]

Before I make any changes to the macro, I want to confirm something with you. I looked at A/C Commercial VRF in your most recent file and if I run the macro, none of the values in sheet2 will change for A/C Commercial VRF. I went through attributes 1 to 30 in row 2 of sheet1 and none match the values in row 2 of sheet2 so there will not be any changes. Actually, Attribute 1 for A/C Commercial VRF does match but the new value is the same as the old value so there is no change. Have I interpreted correctly? Also, I don't follow what you mean by:

the attribute can be in different columns based on that category.
for instance. VOLTAGE Rating in in COL P for 1st catagory but is in COL N for NEXT...

If the same attribute is in a different column for two or more categories, if I understood properly, that shouldn't make any difference in the end result. Is this correct?

 

[trekker1218]

If you look at Sheet 1 sample here:

Product Type

Type Code (P=product type, A=style atribute set)

Primary Attribute 1

Primary Attribute 2

Primary Attribute 3

Primary Attribute 4

Primary Attribute 5

Primary Attribute 6

Primary Attribute 7

Primary Attribute 8

Primary Attribute 9

Primary Attribute 10

Primary Attribute 11

OLD

A/C Commercial VRF

P

Brand Name

BTU

Heating BTU

Voltage Rating

Phase

Refrigerant Type.

SEER

Length.

Width.

Height.

UPC.

New

A/C Commercial VRF

P

Brand Name

Cooling BTU

Heating BTU

Voltage

Phase

Refrigerant

SEER

Length

Width

Height

UPC.

You can see the old value BTU on Sheet 2 here @ Primary Attribute 7:

Sub Category

Primary Attribute 1

VALUE_1

Primary Attribute 2

VALUE_2

Primary Attribute 3

VALUE_3

Primary Attribute 4

VALUE_4

Primary Attribute 5

VALUE_5

Primary Attribute 6

VALUE_6

Primary Attribute 7

VALUE_7

A/C Commercial VRF

Brand Name

Value

Sub Brand

Item Name

VRF Indoor Unit

Type

Slim

Series

Model

BTU

4000 BTU/hr

A/C Commercial VRF

Brand Name

Fujitsu

Sub Brand

Item Name

VRF Indoor Unit

Type

Series

Model

BTU

30000 BTU/hr

A/C Commercial VRF

Brand Name

Fujitsu

Sub Brand

Item Name

Cassette VRF Indoor Unit

Type

Series

Model

BTU

4000 BTU/hr

It should Change to COOLING BTU...

 

[mumps]

I'm sorry but that's not going to work. Macros for the most part rely on set patterns. If the pattern is broken, the macros don't return the correct results. In order for the macro I suggested to work, you have to compare the attributes in order as they appear on both sheets. If we have to jump around to find the OLD value in any column, I don' think that I will be able to help.

 

[trekker1218]

I understand... How about this. Is there anyway to run a lookup with a find and replace.
My logic:
Do a vlookup or index on the Sheet 1 col B (Product Type) I can CONCATENATE it to read OLD-A/C Commercial VRF and NEW-A/C Commercial VRF.
Once a match is found. Index ROW to find matching attribute label like BTU and repalce it with an OFFSET cell directly Below it.
Something like that.
Even if the MACRO wrote the data to a NEW SHEET with new ATTRIBUTES.

I cannot thank you enough for all your efforts.

 

[mumps]

I'm trying another approach. Let me get back to you.

 

[mumps]

Try:

Sub matchData()
    Application.ScreenUpdating = False
    Dim desWS As Worksheet, srcWS As Worksheet, x As Long, fnd As Range, i As Long, rng As Range
    Dim LastRow1 As Long, LastRow2 As Long, myArray1 As Variant, myString1 As String, DataRange1 As Range, lCol1 As Long, fVisRow1 As Long
    Set srcWS = ThisWorkbook.Sheets("Sheet1")
    Set desWS = ThisWorkbook.Sheets("Sheet2")
    LastRow1 = srcWS.Cells(srcWS.Rows.Count, "A").End(xlUp).Row
    LastRow2 = desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Row
    lCol1 = srcWS.Cells(1, srcWS.Columns.Count).End(xlToLeft).Column
    lCol2 = desWS.Rows(1).Find("Primary Attribute 30").Column
    fVisRow1 = srcWS.Range("A2", srcWS.Cells(srcWS.Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Row
    fVisRow2 = desWS.Range("A2", desWS.Cells(desWS.Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Row
    For x = 2 To LastRow1 Step 2
        With srcWS.Cells(1).CurrentRegion
            .AutoFilter 2, srcWS.Cells(x, 2)
        End With
        With desWS.Cells(1).CurrentRegion
            .AutoFilter 1, srcWS.Cells(x, 2)
        End With
        
        Set DataRange1 = srcWS.Range("D" & fVisRow1).Resize(, lCol1 - 3)
        For Each rng In DataRange1.Cells
            myString1 = myString1 & ";|;" & rng.Value & ";|;" & rng.Offset(1).Value
        Next rng
        myString1 = Right(myString1, Len(myString1) - 3)
        myArray1 = Split(myString1, ";|;")
        
        For i = LBound(myArray1) To UBound(myArray1) Step 2
            Set fnd = desWS.Rows(fVisRow2).Find(myArray1(i), LookIn:=xlValues, lookat:=xlWhole)
            If Not fnd Is Nothing Then
                With desWS
                    .Range(.Cells(2, fnd.Column), .Cells(LastRow2, fnd.Column)).SpecialCells(xlCellTypeVisible) = myArray1(i + 1)
                End With
            End If
        Next i
        myString1 = ""
    Next x
    srcWS.Range("A1").AutoFilter
    desWS.Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[trekker1218]

It works for the first category only.
A/C Commercial VRF.
It does not change the next category data.

but you are getting closer with new idea you had.

Run it on the test-file-sorted.xlxs file and look at COL N Row 10 ~ 18 ( Voltage Rating should change to VOLTAGE)

 

[mumps]

I had 2 lines of code in the wrong place. Try:

Sub matchData()
    Application.ScreenUpdating = False
    Dim desWS As Worksheet, srcWS As Worksheet, x As Long, fnd As Range, i As Long, rng As Range
    Dim LastRow1 As Long, LastRow2 As Long, myArray1 As Variant, myString1 As String, DataRange1 As Range, lCol1 As Long, fVisRow1 As Long
    Set srcWS = ThisWorkbook.Sheets("Sheet1")
    Set desWS = ThisWorkbook.Sheets("Sheet2")
    LastRow1 = srcWS.Cells(srcWS.Rows.Count, "A").End(xlUp).Row
    LastRow2 = desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Row
    lCol1 = srcWS.Cells(1, srcWS.Columns.Count).End(xlToLeft).Column
    lCol2 = desWS.Rows(1).Find("Primary Attribute 30").Column
    For x = 2 To LastRow1 Step 2
        With srcWS.Cells(1).CurrentRegion
            .AutoFilter 2, srcWS.Cells(x, 2)
            fVisRow1 = srcWS.Range("A2", srcWS.Cells(srcWS.Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Row
        End With
        With desWS.Cells(1).CurrentRegion
            .AutoFilter 1, srcWS.Cells(x, 2)
            fVisRow2 = desWS.Range("A2", desWS.Cells(desWS.Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Row
        End With
       
        Set DataRange1 = srcWS.Range("D" & fVisRow1).Resize(, lCol1 - 3)
        For Each rng In DataRange1.Cells
            myString1 = myString1 & ";|;" & rng.Value & ";|;" & rng.Offset(1).Value
        Next rng
        myString1 = Right(myString1, Len(myString1) - 3)
        myArray1 = Split(myString1, ";|;")
       
        For i = LBound(myArray1) To UBound(myArray1) Step 2
            Set fnd = desWS.Rows(fVisRow2).Find(myArray1(i), LookIn:=xlValues, lookat:=xlWhole)
            If Not fnd Is Nothing Then
                With desWS
                    .Range(.Cells(2, fnd.Column), .Cells(LastRow2, fnd.Column)).SpecialCells(xlCellTypeVisible) = myArray1(i + 1)
                End With
            End If
        Next i
        myString1 = ""
    Next x
    srcWS.Range("A1").AutoFilter
    desWS.Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub