How to use macro to fetch and replace values in columns?

**rizwanulhasan** · 07-28-2023, 09:34 AM

Hi everyone,

Hope all's well.

For the attached file, i need a macro which can perform the below steps one after another.

1. Column I in sheets "Batches" is not considered as date due to ".". The same is to be changed to "-"

2. Column K in sheets "Batches" is to be fetched from sheet "Locations" using VLOOKUP. Formula entered in cell K4.

3. Column K in sheet "Batches" need to be replaced as per the tables "Site" and "CMO" in sheet "Pack Owner". If value in column K matches with columns A and D in sheet "Pack Owner", then the same should be replaced by values in columns B and E respectively. In short, i want the values in column K in sheet "Batches" should exactly match with the values in column U of sheet "Closed"

4. Column J in sheet "Batches" need to be fetched from sheet "Pack Owner". In case the value in column K in sheet "Batches" falls under column B of sheet "Pack Owner", then column J should populate Site. If it falls under Column E of sheet "Pack Owner", then CMO is to be populated.

Note: Sheets "Locations" and "Pack Owner" will always be hidden and locked with pass Abc@123.

Can someone please help ?

Detailed info as below, if required.

Lemme give a detailed explanation.

The data in sheet "Batches" will be pasted by downloading from an external source. The raw data will contain only the columns A to I.

Now I need to compare data in sheets "Batches" and "Closed" based on below 2 common criteria

Site and
Month

i.e.

1. Column K in sheet "Batches" and column U in sheet "Closed"

and

2. Column M in sheet "Batches" and column Q in sheet "Closed"

Hence i created additional columns in sheet "Batches" i.e. columns J to M.

Now to fulfill the first criteria, i need site name. This is fetched into column K from sheet "Locations" based on column H. However, the values will not exactly match and so there couldn't be a comparison between the 2 sheets. Hence, there's a need to duplicate the names and so i created 2 tables in sheet "Pack Owner".

The columns A and D in this sheet contains names from column B in sheet Locations and columns B and E in this sheet contains names as they are in column U of sheet "Closed". This will align the names in both sheets so that i can compare the data.

This completes the points 3 and 4 in my 1st post.

Last point 4 is to populate column J to know whether the value in column K is a Site or CMO. This can be identified from the columns B and E in sheet "Pack Owner". All the ones under column B are Site while under column E are CMO.

**JohnTopley** · 07-28-2023, 10:02 AM

3. Column K in sheet "Batches" need to be replaced as per the tables "Site" and "CMO" in sheet "Pack Owner". If value in column K matches with columns A and D in sheet "Pack Owner", then the same should be replaced by values in columns B and E respectively. In short, i want the values in column K in sheet "Batches" should exactly match with the values in column U of sheet "Closed"

Check you have not got A & D vs B & E the wrong way round !

And much easier if you reversed A/B and D/E (to use VLOOKUP rathet than Index/Match)

Data volumes : max number of rows?

**rizwanulhasan** · 07-28-2023, 10:16 AM

Hi John,

Appreciate your response.

Apologies for the confusion.

The problem is that i want the values in column K in sheet "Batches" should exactly match with the values in column U of sheet "Closed".

Column K in sheet "Batches" will contain values either from column A or D in sheet "Pack Owner". So to match with Column U of sheet "Closed", they should be replaced with columns B and E of sheet "Pack Owner".

Ex. if value in column K is as in A7 in column A, i.e. DHL Knoxville, then it should be replaced with value in column B i.e. DHL

so DHL Knoxville = DHL

Similarly with column D and E.

Sorry for the data. Deleted some and reattached file in 1st post.

Hope i was able to explain

**JohnTopley** · 07-28-2023, 10:31 AM

Apologies : my error misreading a table!!

Data volumes ?

**rizwanulhasan** · 07-28-2023, 10:33 AM

no worries.

Data Volumes = you mean i should delete more rows?

I already deleted and reattached file in 1st post

**JohnTopley** · 07-28-2023, 11:43 AM

Please Login or Register  to view this content.

**rizwanulhasan** · 07-29-2023, 02:42 AM

Hi John,

Many thanks for the help.

Just few issues.

Macro doesn't fulfill the point 1 mentioned in first post.

VLOOKUP is being applied in column J instead of column K.

Column J doesn't show Site/CMO.

You can run macro in attached file.

Can you please check

Many thanks in advance.

**JohnTopley** · 07-29-2023, 03:07 AM

See attached:

**rizwanulhasan** · 07-29-2023, 03:10 AM

All good except for point 1 and 4

**JohnTopley** · 07-29-2023, 03:48 AM

Don't understand 4,

Column J: Barnard Castle (Pack owner ?)

Column K: BARNARD CASTLE (Site/CMO)

Same for PCI Rockford (J) PCI ROCKFORD (K)

1. Needs to addressed at source: why is there "." ? Find/Replace will address by date will be dd/mm/yyyy (as per file)

**rizwanulhasan** · 07-29-2023, 03:59 AM

Originally Posted by JohnTopley

Don't understand 4,

Column J: Barnard Castle (Pack owner ?)

Column K: BARNARD CASTLE (Site/CMO)

Same for PCI Rockford (J) PCI ROCKFORD (K)

The above is working perfectly as per point 3. Now after this is achieved, 4th point is to be achieved as explained below.

Column J should be populated only with Site or CMO. To decide what should be the value between the two, column K in sheet "Batches" should be searched in columns B and E in sheet "Pack Owner".

If the value in column K in sheet "Batches" is in column B of sheet "Pack Owner", then column J should populate Site.

If the value in column K in sheet "Batches" is in column E of sheet "Pack Owner", then column J should populate CMO.

BARNARD CASTLE is in column B of sheet "Pack Owner" and hence column J should populate as SITE.

PCI ROCKFORD is in column E of sheet "Pack Owner" and hence column J should populate as CMO.

Originally Posted by JohnTopley

Needs to addressed at source: why is there "." ? Find/Replace will address by date will be dd/mm/yyyy (as per file)

The report i download gives date in 29.07.2023 format and not 29-07-2023. Hence the need to change . to -

Hope i was able to clarify

**maxpit** · 07-29-2023, 08:35 AM

Hi rizwanulhasan,

to replace the dot, all that is needed is the following code to be inserted immediately after the start of the For loop:

Please Login or Register  to view this content.

but still I cannot understand the description: what is the relationship between "Location Name" and "Site/CMO".
I would like to ask you if you could kindly clarify it better (maybe I misunderstood, but in Tabel3 no "Location Name" is matched with Site/CMO)

Bye

**rizwanulhasan** · 07-29-2023, 09:59 AM

Hi @maxpit

Thanks for the above code. However, it changes the month for few cells such as I11. The month changes from June to September. Also it keeps some of the time format in 12hour and some in 24 hour format. Can you please check?

Originally Posted by maxpit

but still I cannot understand the description: what is the relationship between "Location Name" and "Site/CMO".
I would like to ask you if you could kindly clarify it better (maybe I misunderstood, but in Tabel3 no "Location Name" is matched with Site/CMO)

Bye

Apologies. Perhaps i am confusing all. Lemme give a detailed explanation.

The data in sheet "Batches" will be pasted by downloading from an external source. The raw data will contain only the columns A to I.

Now I need to compare data in sheets "Batches" and "Closed" based on below 2 common criteria

Site and
Month

i.e.

1. Column K in sheet "Batches" and column U in sheet "Closed"

and

2. Column M in sheet "Batches" and column Q in sheet "Closed"

Hence i created additional columns in sheet "Batches" i.e. columns J to M.

Now to fulfill the first criteria, i need site name. This is fetched into column K from sheet "Locations" based on column H. However, the values will not exactly match and so there couldn't be a comparison between the 2 sheets. Hence, there's a need to duplicate the names and so i created 2 tables in sheet "Pack Owner".

The columns A and D in this sheet contains names from column B in sheet Locations and columns B and E in this sheet contains names as they are in column U of sheet "Closed". This will align the names in both sheets so that i can compare the data.

This completes the points 3 and 4 in my 1st post.

Last point 4 is to populate column J to know whether the value in column K is a Site or CMO. This can be identified from the columns B and E in sheet "Pack Owner". All the ones under column B are Site while under column E are CMO.

Sorry for the confusion.

**maxpit** · 07-29-2023, 10:53 AM

Hi rizwanulhasan,

with Italian settings it works, with English settings it reverses month/day:

Please Login or Register  to view this content.

Try the above code and let me know.

Bye

**AliGW** · 07-29-2023, 10:55 AM

Maxpit - FYI British English settings (and most other English settings) are the same as Italian. Only English US uses month - day - year.

**rizwanulhasan** · 07-29-2023, 11:00 AM

Originally Posted by maxpit

Hi rizwanulhasan,

with Italian settings it works, with English settings it reverses month/day:

Please Login or Register  to view this content.

Try the above code and let me know.

Bye

Sure, i'll try maxpit.

However, may i know where exactly to put it in VBA?

As mentioned by you earlier, i pasted it right after the For line, but it doesn't work

**JohnTopley** · 07-29-2023, 11:53 AM

Please Login or Register  to view this content.

**maxpit** · 07-29-2023, 02:09 PM

Hi rizwanulhasan,

it is an oversight on my part

Please Login or Register  to view this content.

The above code as modified, in the highlighted part, must be inserted immediately after the start of the For loop.

Bye

**rizwanulhasan** · 07-29-2023, 02:57 PM

Many thanks John.

Points 3 and 4 are now achieved perfectly. Just 1 issue.

The macro is taking a very very long time to run. I even tried restarting PC, but its still the same.

Your code in post # 6 was completing in a second. However, this one is taking at least 3-4 mins and sometimes excel ends up in Not Responding

**rizwanulhasan** · 07-29-2023, 03:02 PM

Originally Posted by maxpit

Hi rizwanulhasan,

it is an oversight on my part

Please Login or Register  to view this content.

The above code as modified, in the highlighted part, must be inserted immediately after the start of the For loop.

Bye

Many thanks maxpit.

This code works perfectly for the file in post # 8

and the code runs in a second.

Now just the 4th point of post # 1 is to be achieved.

**jindon** · 07-29-2023, 09:18 PM

1) Convert Col.K to serial date from string date and format the cell. As well as Col.J to the number, not string.
2) OK.
3) No change, since not match.
4) No output as no match from 3).

Please Login or Register  to view this content.

**rizwanulhasan** · 07-30-2023, 01:14 AM

Hi jindon,

Thanks for your response.

Originally Posted by jindon

1) Convert Col.K to serial date from string date and format the cell. As well as Col.J to the number, not string.

Sorry i didn't get your instructions

**jindon** · 07-30-2023, 01:28 AM

No, that was not the instructions.

That is what the code does.
So, just run the code.

**rizwanulhasan** · 07-30-2023, 02:24 AM

Oh. Sorry i thought those as instructions.

I ran code. 1 and 2 points works perfectly as per my first post but not 3 and 4.

**jindon** · 07-30-2023, 02:29 AM

3. Column K in sheet "Batches" need to be replaced as per the tables "Site" and "CMO" in sheet "Pack Owner". If value in column K matches with columns A and D in sheet "Pack Owner", then the same should be replaced by values in columns B and E respectively. In short, i want the values in column K in sheet "Batches" should exactly match with the values in column U of sheet "Closed"

As I said already in my post #21, you don't have any matched data after vlookup in col.K with 2 tables in "Pack owner", so non change.

If my understanding it not correct, you should upload the workbook with your result clearly showing the reason.

**rizwanulhasan** · 07-30-2023, 02:41 AM

My apologies Jindon. I got your point now.

All works now, except the 3rd point of my post 1.

Column K in sheet "Batches" not being replaced as per columns B and E in sheet "Pack Owner"

**jindon** · 07-30-2023, 02:55 AM

OOps, missed one line bold.

Please Login or Register  to view this content.

and one more change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

**rizwanulhasan** · 07-30-2023, 03:10 AM

Please Login or Register  to view this content.

i have added above line and everything's working perfectly.

Do you still want me to make the below change i.e.

Please Login or Register  to view this content.

**jindon** · 07-30-2023, 03:14 AM

Because you have blank in Site A4 with the replace string of BORONIA in B4.

That means blank in Col.K should be replaced with BORONIA.
If not, you don't need to change.

**rizwanulhasan** · 07-30-2023, 03:30 AM

the first code is fine. All's perfect now.

Many many thanks @jindon @JohnTopley @maxpit

**maxpit** · 07-30-2023, 04:21 AM

glad to have been useful to you

How to use macro to fetch and replace values in columns?

LinkBack

Thread Tools

Rate This Thread

Display

How to use macro to fetch and replace values in columns?

Re: How to use macro to fetch and replace values in columns?

Re: How to use macro to fetch and replace values in columns?

Re: How to use macro to fetch and replace values in columns?

Re: How to use macro to fetch and replace values in columns?

Re: How to use macro to fetch and replace values in columns?

Re: How to use macro to fetch and replace values in columns?

Re: How to use macro to fetch and replace values in columns?

Re: How to use macro to fetch and replace values in columns?

Re: How to use macro to fetch and replace values in columns?

Re: How to use macro to fetch and replace values in columns?

Re: How to use macro to fetch and replace values in columns?

Re: How to use macro to fetch and replace values in columns?

Re: How to use macro to fetch and replace values in columns?

Re: How to use macro to fetch and replace values in columns?

Re: How to use macro to fetch and replace values in columns?

Re: How to use macro to fetch and replace values in columns?

Re: How to use macro to fetch and replace values in columns?

Re: How to use macro to fetch and replace values in columns?

Re: How to use macro to fetch and replace values in columns?

Re: How to use macro to fetch and replace values in columns?

Re: How to use macro to fetch and replace values in columns?

Re: How to use macro to fetch and replace values in columns?

Re: How to use macro to fetch and replace values in columns?

Re: How to use macro to fetch and replace values in columns?

Re: How to use macro to fetch and replace values in columns?

Re: How to use macro to fetch and replace values in columns?

Re: How to use macro to fetch and replace values in columns?

Re: How to use macro to fetch and replace values in columns?

Re: How to use macro to fetch and replace values in columns?

Re: How to use macro to fetch and replace values in columns?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Lookup formula taking time to fetch values, need a fast macro

[SOLVED] Multiple rows/columns values compare and fetch data

[SOLVED] Unable to fetch values in two columns into a single cell with comma seperator

Trimming the data and fetch only numeric values from 3 columns

[SOLVED] Macro to scan rows and columns to replace values with a blank - Followup question

[SOLVED] Macro to scan rows and columns to replace values with a blank

How to Fetch a table values in a webpage to Excel sheet using VBA Macro??? Help Me

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions