Pull data to new sheet from a specific data set (column)

[joach]

Hi All,

I really hope some genius can help me.

Excel in Office 365.

I have a spreadsheet that contains an array of data (column A-Q and row 2-10,000)

The data that I have is generated from 34 sheets within the spreadsheet (I have converted each sheet into a table) and have been collated all of the data on one sheet using a query.

The data that I'm interest in is located in Column E which contains the general descriptions of the product. I have set up condition formatting that responds to key words i.e hat, beanie, etc, which highlights the cell green.

what I want to do is extract the entire row (A-Q) when the conditional formatting is triggered for a key word and export the entire row into a new sheet named "headwear".

In the future I want to be able to set up separate sheets for different categories such "outerwear", "accessories", etc.


The issue I have is the descriptions coming in (located in column E) are not a one word categorization 'beanie' they are more of a sentence / paragraph, an example would be.

"Hi thank you for your time, I have an issue with the beanie that I bought a couple weeks ago the tread is coming loose, could I please arrange a replacement"

As a result I want to be able to identify the word 'beanie' from the text and then export the row it is associated with into a separate sheet, much like a filter.

Is this possible and if it is can someone please help me out

Joach

 

[RoryA]

Welcome to the forum.

It sounds like you could simply use the FILTER function for that using ISNUMBER(SEARCH("beanie",E:E)) as the filter criteria.

 

[joach]

Welcome to the forum.

It sounds like you could simply use the FILTER function for that using ISNUMBER(SEARCH("beanie",E:E)) as the filter criteria.

Hi Rory,

Thank you for the reply and introduction to forum.

Sorry for my ignorance but do I set up the filter on the new sheet?

If I have multiple terms that I want to search for i.e beanie, hat, cap, etc am I able to add them into the same filter or do I have to make a separate filter for each term.

If I can use the same filter what is the formatting that I need to use.

Thanks for your help thus far.

Joach

 

[RoryA]

For multiple criteria, it might be simpler to use an advanced filter but words like 'hat' are problematic when you want to do a contains search, since they appear as part of words like 'what' or 'that'.

 

[joach]

For multiple criteria, it might be simpler to use an advanced filter but words like 'hat' are problematic when you want to do a contains search, since they appear as part of words like 'what' or 'that'.

Hey Rory,

thanks for the reply.

Yeah that is something that I was considering however I thought I may be able to get around that by filtering for ' hat ' by applying spaces either side of the word or in front or after the word.

Are you able to search for phrases such as 'woolen hat' which incorporates spaces?

Could you help me formulate a filter to get the general idea across and then I can tweak it to see how I can get it to work?

Cheers,

Joach

 

[RoryA]

You can search for anything you like

The only issue I see with using spaces around the word is that it looks like you have punctuation in your text, so searching for " hat " wouldn't match something like " hat, which"

Anyway, for an advanced filter, there are two approaches - you need a separate criteria range for either one. For a basic approach, you'd put the relevant column header in the first cell of the criteria range, then underneath that you would list your search values enclosed in asterisks (that's the wildcard symbol to allow finding the text anywhere in the cell):

Description
* beanie *
* hat *
* cap *

The second approach is more flexible as it allows you to use formulas to search, but it's a little less obvious to set up. You still need a criteria range but your header cell should not match any column names in the source table. Under that you have a formula cell in which you refer to the first data cell in the column you are filtering, using relative referencing. So if you are filtering column A (with a header in A1), your formula would refer to A2 for your comparison. The advanced filter will then effectively iterate down the column of data replacing A2 with A3, A4 etc and return data only where your formula evaluates to True. So for example:

Book1
	A	B	C
1	Description		MyCriteria
2	Hi thank you for your time, I have an issue with the beanie that I bought a couple weeks ago the tread is coming loose, could I please arrange a replacement		TRUE
3	I bought some trousers yesterday that I need to return - can you help?
4	Hi my hat doesn't fit quite right, can I get an exchange?
Sheet3

results in this output:

Description
Hi thank you for your time, I have an issue with the beanie that I bought a couple weeks ago the tread is coming loose, could I please arrange a replacement
Hi my hat doesn't fit quite right, can I get an exchange?

Hopefully that makes some sense?

 

[joach]

You can search for anything you like

The only issue I see with using spaces around the word is that it looks like you have punctuation in your text, so searching for " hat " wouldn't match something like " hat, which"

Anyway, for an advanced filter, there are two approaches - you need a separate criteria range for either one. For a basic approach, you'd put the relevant column header in the first cell of the criteria range, then underneath that you would list your search values enclosed in asterisks (that's the wildcard symbol to allow finding the text anywhere in the cell):

Description
* beanie *
* hat *
* cap *

The second approach is more flexible as it allows you to use formulas to search, but it's a little less obvious to set up. You still need a criteria range but your header cell should not match any column names in the source table. Under that you have a formula cell in which you refer to the first data cell in the column you are filtering, using relative referencing. So if you are filtering column A (with a header in A1), your formula would refer to A2 for your comparison. The advanced filter will then effectively iterate down the column of data replacing A2 with A3, A4 etc and return data only where your formula evaluates to True. So for example:

Book1
	A	B	C
1	Description		MyCriteria
2	Hi thank you for your time, I have an issue with the beanie that I bought a couple weeks ago the tread is coming loose, could I please arrange a replacement		TRUE
3	I bought some trousers yesterday that I need to return - can you help?
4	Hi my hat doesn't fit quite right, can I get an exchange?
Sheet3

View attachment 69530

results in this output:

Description
Hi thank you for your time, I have an issue with the beanie that I bought a couple weeks ago the tread is coming loose, could I please arrange a replacement
Hi my hat doesn't fit quite right, can I get an exchange?

Hopefully that makes some sense?

Hi Rory,

Thanks for the great description / direction.

It seems that the phrases I have place in my filters column such as *beanie* are being picked up outside of the description column of the main table, i.e company name column "Beanie Co".

Could this be due to the fact that my data is in a table format?

Can advance filters be used on Tables?

 

[RoryA]

Yes they can be used in tables. The criteria will only be applied to the column specified in the header cell of the criteria range (assuming you went with the first option).

 

[joach]

Yes they can be used in tables. The criteria will only be applied to the column specified in the header cell of the criteria range (assuming you went with the first option).

I figured out that I had entered a conflicting criteria.... Its now working

 

[joach]

Yes they can be used in tables. The criteria will only be applied to the column specified in the header cell of the criteria range (assuming you went with the first option).

hi Rory,

Sorry to bother you, I posted the following on another tread however no one has answered it, could you have a look at it for me.

------

Hi,

I'm wanting to use advance filters to filter my data set.

I am wanting to follow up with the customer 8 months, 20 months and 56 months after the purchase date, but if the date is equal to or more than 12, 24 and 60 months from the purchase date I don't want to see the data in the new sheet.

In written terms I want to see the following data however I don't know how to type it in an Excel format / formulas.

follow up
1 year	if todays date is between 8 months and 12 months from the purchase date
2 year	if todays date is between 20 months and 24 months from the purchase date
5 year	if todays date is between 56 months and 60 months from the purchase date

Could you please help me with the formatting / formulas for the above criteria.

In addition to this once the filtered data is on the new sheet 'follow up' I want to use conditional formatting with a colour gradient to show the following - green being the lower number (i.e 8 months), yellow being the mid point (i.e 10 months) and red being the deadline (i.e 12months).

If you could please help with the above that would be amazing.

If you need anything else please let me know.