How to fetch data from a sheet based on conditions?

**rizwanulhasan** · 04-16-2024, 06:15 AM

Hi all,

Hope all are well.

Need a help with a macro in attached sheet.

1. Macro is to be run from sheet "Closed"

2. The number in column A in sheet "Closed" needs to be searched under column A in sheet "Extract - Incidents" and based on this common number, the data in column column AC (Resolution Code) in sheet "Closed" is to be fetched from column M (Resolution code) in sheet "Extract - Incidents".

3. Similarly data under column AD (Resolution Notes) in sheet "Closed" to be fetched from column N (Resolution notes) in sheet "Extract - Incidents".

4. Both columns AC and AD to be auto filled up to last filled row.

4. All data in sheet "Extract - Incidents" starting from row 4 to be deleted while retaining 3rd row.

5. Then cursor to to be placed at cell O3 (First Closed) of sheet "Closed"

Note:

i. Fetched data to be pasted as values (not as formula)

ii. Some numbers will not be found, so the result should be N/A. Expected results in file.

iii. This is a daily activity where new data will be uploaded in the sheet "Extract - Incidents".

Can someone help with the solution please?

**rizwanulhasan** · 04-16-2024, 06:41 AM

Modified requirement in post # 1. Table in sheet "Extract - Incidents" converted to range.

**JohnTopley** · 04-16-2024, 07:53 AM

Password???

All data in sheet starting from row 4 to be deleted while retaining 3rd row.

Which sheet?

**rizwanulhasan** · 04-16-2024, 09:14 AM

Hi John,

Thanks for your response.

File unprotected and reattached in post #1.

All data in sheet "Extract - Incidents" starting from row 4 to be deleted while retaining 3rd row.

Sorry for the trouble.

**JohnTopley** · 04-16-2024, 09:26 AM

Please Login or Register  to view this content.

**sintek** · 04-16-2024, 09:49 AM

Please Login or Register  to view this content.

**rizwanulhasan** · 04-16-2024, 12:06 PM

@JohnTopley @sintek

Thanks a bunch. Its working perfectly.

**sintek** · 04-18-2024, 03:16 AM

Glad to have contributed...Tx for rep +

**rizwanulhasan** · 04-24-2024, 06:43 AM

Hi again,

Just a small help.

I want to add one more condition in below code.

Please Login or Register  to view this content.

The number in column A in sheet "Closed" needs to be searched under column A in sheet "Extract - Incidents" and based on this common number, the data in column column N (Updated) in sheet "Closed" is to be fetched from column J (Resolved) in sheet "Extract - Incidents". However, this search should be only for the values which has "Incident" under column B (Task type) in sheet "Closed". For others, values in column N should remain as it is. Finally, column N to be auto filled up to last filled row.

Can someone please help

**sintek** · 04-24-2024, 08:18 AM

want to add one more condition

Please Login or Register  to view this content.

**rizwanulhasan** · 04-24-2024, 08:50 AM

Hi Sintek,

Thanks for your response.

1 issue faced.

If the value under column B is not "Incident", then column N is being populated as 00-Jan-1900 00:00:00, whereas it should retain original value, as mentioned in previous post.

Also, i forgot to mention that if an incident i snot found in sheet "Extract - Incidents", then result should be N/A. Sorry for the miss.

**sintek** · 04-24-2024, 09:14 AM

See amended above...

**rizwanulhasan** · 04-24-2024, 09:23 AM

Attached

**sintek** · 04-24-2024, 09:29 AM

What happens if it finds the number that has Incident task but the date in Col J in Extract sheet is empty...
Why is this date empty anyway...?

**rizwanulhasan** · 04-24-2024, 09:36 AM

Good question

In that case, the value can be blank. The date is empty because the incident is not yet resolved. Only if column G in sheet "Closed" is either Resolved or Closed, then date under column J in sheet "Extract - Incidents" will be populated, else will be empty.

**sintek** · 04-24-2024, 09:40 AM

Geez...And the plot thickens...Don't have time right now...Need to rush off...Will check in later tonight...

**rizwanulhasan** · 04-24-2024, 09:53 AM

No worries, take your time

**JohnTopley** · 04-24-2024, 10:43 AM

Late to the party ...

Please Login or Register  to view this content.

**sintek** · 04-24-2024, 03:29 PM

As your dataset is probably large...Looping data in array and then writing back to sheet,will be faster than formula and evaluate function...
This incorporates initial and additional requirement...

Please Login or Register  to view this content.

**daboho** · 04-24-2024, 04:18 PM

Perhab

Please Login or Register  to view this content.

**sintek** · 04-25-2024, 05:13 AM

Just for closure...Evaluate incorporating all requirements...

Please Login or Register  to view this content.

**rizwanulhasan** · 04-25-2024, 08:01 AM

Originally Posted by sintek

Just for closure...Evaluate incorporating all requirements...

Please Login or Register  to view this content.

Hi sintek,

Some of the values in column AD are not being fetched "Extract - Incidents" sheet

**rizwanulhasan** · 04-25-2024, 08:02 AM

Hi @John and @Daboho,

Thanks for the response.

The macro in post 18 and 20 works however in a sheet with 1400 rows, it took 10 minutes for the macro to run and eventually excel hung up

**sintek** · 04-25-2024, 08:14 AM

Some of the values in column AD are not being fetched "Extract - Incidents" sheet

Works for me as it should for me...See attached...

Post 19 also works as it should...And will be faster than 18 & 20

**rizwanulhasan** · 04-25-2024, 08:39 AM

Strange. AC and AD are not being fetched properly in attached file.

Post 19 is perfect but slower than code in post 21

**sintek** · 04-25-2024, 08:43 AM

Your sheet is filtered...

**rizwanulhasan** · 04-25-2024, 09:07 AM

i tried even without the sheet filtered, yet fields missing

**sintek** · 04-25-2024, 09:11 AM

yet fields missing

Please upload a small sample showing which fields are missing...All works with your original sample file...

**rizwanulhasan** · 04-25-2024, 09:29 AM

Attached and highlighted in yellow

**JohnTopley** · 04-25-2024, 09:41 AM

Please Login or Register  to view this content.

Schoolboy error!

**sintek** · 04-25-2024, 10:02 AM

Attached and highlighted in yellow

Oh okay, now I get it...There is a character limit that vlookup can handle...Your info exceeds this limit...255...reduce it and you will see all works as it should...

OR revert to Post 19...
OR index Match...

**JohnTopley** · 04-25-2024, 11:11 AM

Please Login or Register  to view this content.

Array processing ....

**sintek** · 04-25-2024, 11:44 AM

This will be faster than Post 19...

Please Login or Register  to view this content.

**daboho** · 04-25-2024, 11:56 AM

Perhab

Please Login or Register  to view this content.

**rizwanulhasan** · 04-26-2024, 12:34 AM

@John,

Post # 32 is faster and perfect.

@sintek

All good with post # 33 except that dates appear in 20-12-2023 01:07:01 PM instead of 20-Dec-2023 01:07:01 PM and sheet "Extract - Incident" doesn't clear.

@daboho

Getting "Compile error: Variable not defined"

Thanks a bunch to everyone for the prompt response

**JohnTopley** · 04-26-2024, 02:32 AM

Thank you for the rep.

And well done on your reply where you addressed each respondent individually: a model others should follow

**AliGW** · 04-26-2024, 02:33 AM

Absolutely, John - the epitome of completeness and fairness.

**sintek** · 04-26-2024, 03:03 AM

All good with post # 33 except that dates appear in 20-12-2023 01:07:01 PM instead of 20-Dec-2023 01:07:01 PM

slight snippet addition...

Please Login or Register  to view this content.

sheet "Extract - Incident" doesn't clear.

That snippet was cmmented out for testing...

Please Login or Register  to view this content.

Glad you got it sorted though and so many options...Tx for rep +

Please Login or Register  to view this content.

**rizwanulhasan** · 04-26-2024, 03:33 AM

@sintek

Thanks again.

@John @ AliGW

Thanks both. Will follow it always

How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Re: How to fetch data from a sheet based on conditions?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How to fetch data from other sheets in one sheet based on quarters?

Bookmarks

Bookmarks

Posting Permissions