Find, group and rename data based on a word (of several) in cell

[Oth]

Hi all, I am new to the board and this is my first own post..
First I need to thank you all for the help you have already given me over the years when I have searched for solutions - this is truly an amazing source of information!!

I have searched long for the solution but I might have used the wrong terminology..?

I basically want to group stuff from table (A-D) of 10K items, rename them according to a template and create a new shorter list (G-J). ((Every month I will get a new A-D list))

The words I am looking for (in this exmple the fruits) can be anywhere in the cell. The cells might include many kind of signs (*/-., etc).
In some situations (as in A5) two of the searched words might be in the cell - are there options on how to solve that?
I understand I might need to create a lookup/group list of some sort but am not sure how to do it.
I can not change the original list unfortunately - so any clean up of that
has to be simple enough for me to handle it.

If you have time and ability to help, please remember I am a novice on excel.
Thanks in advance.

I am now attempting to use MR excel html to show you a view of what the problem is (no formulas in the example):

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Original list						Result in new list
2	Sold products	Jan	Feb	March		how I want it grouped:	Product groups	Jan	Feb	March
3	Applepie	2	5	2		Apple	Apple products	24	32	27
4	Apples	4	2	3		Banana (what happens to the Apple and banana pie?)	Banana products	26	14	7
5	pie.-Apple and banana	2	8	6		Lemon + Meat	Other products	24	24	21
6	Applesauce	7	5	7		Any other	Specials	5	6	8
7	XXapple-sauce	6	4	9			Total sum	79	76	63
8	apple* cjd* juice	3	8	0
9	Candy kkns	2	2	3
10	Bananas	19	7	1
11	bananacurry	4	3	5		Searching for a solution compatible with Excel 2003
12	chicken with bananas	3	4	1		If not doable in 2003 - is it doable in 2007 or 2010?
13	lemon	9	7	0		Original list is ca 10k items
14	lemon-curd	1	3	4		Colors are only for showing here.
15	chicken with lemons	2	3	5
16	Meatloaf	3	3	3
17	Meat loaf	4	4	4
18	Meat-pie	5	4	5
19	potatoes	3	4	5
20	Total	79	76	63
Sheet1

Cell Formulas
Range	Formula
H7	=SUM(H3:H6)
I7	=SUM(I3:I6)
J7	=SUM(J3:J6)
B20	=SUM(B3:B19)
C20	=SUM(C3:C19)
D20	=SUM(D3:D19)

 

[Gary McMaster]

Would custom autofilters be of any use or does this have to be completely automated?

Gary

 

[Oth]

Thanks for your answer!

To be honest, I don't know. Which ever solution is applied, it needs to be able to handle the original list of ca 10'000 rows + I expect the grouped list to be ca 3-5000 rows.
/C

 

[Gary McMaster]

A custom autofilter, for example, would show you all of the rows that contain the word "Apple" anywhere in the cell (of any selected column). It would tell you how many rows meet this criteria (10K, 65K doesn't matter). You would create the data you have shown in columns G:J by hand.

Search help for "custom auto filter" if you care to give it a try. Use the "contains" operator

Gary

 

[Oth]

Thanks Gary.
Do I need to do this for the whole list every time the original list is updated?
If yes, then I think I do need an (at least partly) automated solution since the list is updated often.
/C

 

[Gary McMaster]

Sorry, you would have to re-create any summary data (col G:J) every time.

You could add a subtotal formula. For example at the bottom of your column B in the original example =SUBTOTAL(9,B3:B19)

Subtotaling would add up the values in column B for the data that remains displayed (that contain "apple" sticking with the original example). You could use the subtotal function in as many columns as you need.

Gary

 

[Oth]

ok, good, thats an improvement, but I will still need to edit the filter ca 3-5000 times (since the total grouped list has 3-5000 entries). Right?
I can definitely do that once or twice - but it would be to much to do it every other week. Perhaps it can not be done automated in excel?

 

[Gary McMaster]

The technique we've been discussing is practical for a few filters. You've got a lot more patience than I do if you willing to do several thousand a week

This can be done via code but it would probably get rather involved. If you don't have programming experience it would probably be very difficult.

Gary

 

[Gary McMaster]

You could get a feel for the code required by turning on the macro recorder and repeating the steps you have probably already performed as a result of this discussion. The macro recorder usually generates much more code than is really needed. If you want to give it a shot, there are many people in this group that will be happy to give you tips on how to improve the code. Just ask clear and specific questions about small pieces of the code and I'm sure you'll get plenty of help.

Gary

 

[Oth]

Hehe, patience or not, somethings just has to be done, in this case I prefer the easier way if at all possible

My hope was to get a hint on which way to go - then I'll give that to my friend (she is busy now) who has more excel coding experience than me - so we can test it.