Finding values within a row, replacing with other values if match

[Subspace]

Hi,

I'm very new to this, so my apologies. I've tried searching for the answer with no luck, although I'm sure this has been answered many times, I just don't know the correct search parameters.

Basically, I want to search a row, if data matches in another workbook, then replace with data from the other workbook. I can't work out how to search a row of data. See attached image.
Something like, IF A1+B1+C1+D1+F1= Same data from another workbook THEN replace column 'E1' with predetermined information (Or replace entire row is fine too, although I would prefer if B1 wasn't affected).

I have a large source workbook to work from and doing this manually is time consuming. I also have to do it daily.

The data always has the same information in each column, but the rows could be different. Is there a way for VBA to search each row of my new workbook and replace it with data from my source workbook if it matches one of the rows in my source workbook?

I'd attempt to write what I want, but I'll fumble it.
So instead, I found this, which I think is right, but I want it to search more of the row before replacing the "E column" in the row.

VBA Find and Replace using another workbook

Hi, I am trying to do a find and replace part of a string in the selected cell values in Book1-Sheet1 using Book2-Sheet1. Book 2 contains what to search for in a column 1 and the replacement is in the adjacent column. The Find and Replace Values is a long list of over more than 10,000 Rows Can...

www.mrexcel.com

Any thoughts would be appreciated.

 

[Akuini]

I have a large source workbook

1. About how many rows of data on each workbook?

THEN replace column 'E1' with predetermined information

2. What predetermined information?

3. Can you give an example of data on both workbooks?

 

[Subspace]

1. About how many rows of data on each workbook?


2. What predetermined information?

3. Can you give an example of data on both workbooks?

Hi Akuini,

Thank you for your response.

1. The amount of rows varies between 50 and 200 daily, but the columns are always 12 and have the same information in them day by day.

2. The information being pulled from one work book to another.
I want to read a row from workbook 1, find it in workbook 2 and if enough columns in the row match, replace row in workbook 1.
Rinse repeat for all rows

3. The data is numbers, names and more numbers
The data is the same each day, like it might say:
| 3 | James | 25125 | For exchange (or some other wording)
But I want them reformatted to say:
| 3 | Louis | 25125 | For exchange |

In the OP I attached an image of a "before" and "After" assume instead they were in two separate workbooks - I suppose as I say this, I could post the information into another sheet in workbook 1 if it's impossible to pull data between workbooks
The image in the OP is:
Before: Rows 1,2 & 3
to be replaced with
After: Rows 6, 7 & 8

This could be the ramblings of a madman, but I hope this makes sense to someone.

Thank you again Akuini

-Subspace

 

[Akuini]

It's a bit difficult for me to understand your explanation.
Could you upload samples of both workbook (without sensitive data) to a sharing site like dropbox.com or google drive?

 

[Subspace]

It's a bit difficult for me to understand your explanation.
Could you upload samples of both workbook (without sensitive data) to a sharing site like dropbox.com or google drive?

Hi Akuini,

I've uploaded documents called TEST1 and TEST2. I want a script that reads each column in TEST1, compares it to TEST2, then replaces TEST1 with TEST2's data.
The main information is that the "parts" and "code" columns line up.

I could copy the TEST2 data into another worksheet within TEST1's workbook if it's not possible otherwise, but I'd have to do it daily. TEST1 can be active and TEST2 always has the same name.

TEST1.xlsx

TEST1 Parts,Names,Info,Code,Names,Date 95,Atticus Yu,84811,Atticus Yu,11/23/2022 561,Maddison Rosario,11/23/2022 57,84871,Eve Dominguez,11/23/2022 9,H Whitney,96797,Hugo Whitney,11/23/2022 86,I VANG,45497,85497,Irvin Vang,11/23/2022 12,YAIR,85065,Yair Arias,11/23/2022 48,Haiden Ritter,11/23/2022 10

docs.google.com

TEST2.xlsx

TEST2 Amount,Particulars,Code,Reference,Other Party Parts,Names,Info,Code,Names 95,Atticus Yu,85811,Atticus Yu 561,22695,Maddison Rosario 48,23665,Haiden Ritter 10,25106,25106,Sanai Boyle

docs.google.com

Thank you again for your time.

 

[Subspace]

Hi Akuini,

I've uploaded documents called TEST1 and TEST2. I want a script that reads each column in TEST1, compares it to TEST2, then replaces TEST1 with TEST2's data.
The main information is that the "parts" and "code" columns line up.

I could copy the TEST2 data into another worksheet within TEST1's workbook if it's not possible otherwise, but I'd have to do it daily. TEST1 can be active and TEST2 always has the same name.

TEST1.xlsx

TEST1 Parts,Names,Info,Code,Names,Date 95,Atticus Yu,84811,Atticus Yu,11/23/2022 561,Maddison Rosario,11/23/2022 57,84871,Eve Dominguez,11/23/2022 9,H Whitney,96797,Hugo Whitney,11/23/2022 86,I VANG,45497,85497,Irvin Vang,11/23/2022 12,YAIR,85065,Yair Arias,11/23/2022 48,Haiden Ritter,11/23/2022 10

docs.google.com

TEST2.xlsx

TEST2 Amount,Particulars,Code,Reference,Other Party Parts,Names,Info,Code,Names 95,Atticus Yu,85811,Atticus Yu 561,22695,Maddison Rosario 48,23665,Haiden Ritter 10,25106,25106,Sanai Boyle

docs.google.com

Thank you again for your time.

* I mean each ROW: " I want a script that reads each row in TEST1"