Formula to merge similar Data

[afriedman]

I am trying to figure out a clever way to merge this data without losing any information (this may be a simple solution that I am just nto thinking of).

Basically, I have a list of keywords and many of them repeat. They are all in categories, sometimes in differnt top level categories.

What I am trying to do is:
The keyword allergy (row 2) has 210 visits per month and is in the category Food.
row 3 is the same info but has an additional sub category of Food & Food information
row 4 again is the same with an additional sub category Baking and Spices
etc.

the problem is, of rows 2,3,4, and 5, I really only want 5 since it provides the most information. But that being said, I dont want to loose the other ways that keyword is being categorized. Meening, I still want to see row 6.

Anyone have a clever merging formula that can do this?

[afriedman]

No one has any ideas?!?!?

[Gunther Maplethorpe]

I'm confused as to exactly what you want. Are you saying that, for each different keyword, you'd like to see only the row that has the most sub-categories listed? In your example sheet, that would be rows 5, 13, and 21...?

[afriedman]

Yes, however don't forget row 6, 7, 10, 14, etc.

Basically, every-time there is something unique about it, I want it to show. But I want it to show the deepest of that variation.

I awnt to eliminate redundancy.

In the example I sent, row 5 encorporates row 4, row 3 and row 2 so those arent necessary. What I am looking for is a formula that can merge everything in that way.

Anyone have any thoughts?

[ChemistB]

Okay, what about different keywords ie. rows 5 and 13. You would want both of them kept? Why is there a 28 in B18 instead of a 36? Is that a typo?

[afriedman]

Yes I would want both of thsoe kept and I believe that is a typo... it should be consistent.

[NBVC]

Maybe a bit too basic.. but who knows...just a guess...

Assuming you only have the 3 subcategories...using a couple of helper columns...

In G2 copied down:

=SUMPRODUCT(COUNTIF(D2:F2,D1:F1))

in H2 copied down:

=OR(G2=2,AND(G1=2,G2=1),AND(G1<>"",G1=0,G3=0))

then filter for FALSE and delete those rows...

[Jacques Grobler]

Hi afriedman,

I do not see your example that you attached, can you attached it again so I can have a look.

[NBVC]

This post is from February 2011!

[afriedman]

Its true this is old and its solved as well.
NBVC, I tried to edit the first post to mark as solved but I dont see that option. Any advice?

[NBVC]

Done. Thanks.

[Jacques Grobler]

Solved already,

Alright, I just thought that with the thread still being open and no reference is made to it being solved, that, well, it is not solved yet.

But good, glad to hear it is...