this is a hard one... Extracting info from raw data based on list of criterion

Not perfect, maybe could give you an idea for the results....

Hi,

Glad to have found this forum.. Im really in a fix here.
prolly confused you too..

I did up a sample in the working sheet. Hope this helps you see what I am trying to achieve..
My raw data will be alot more that what I've provided therefore i really need a template which can help me formulate the answer each time I receive new raw data.

Thanks

hi azumi,

that was fast! thanks.. i've also uploaded a sample of what I actually want since I realised my post was kinda confusing to me and possibly everyone too..
Can you look at that too? Thanks.

Updating file with your situation but still confused with your condition

Originally Posted by azumi

Updating file with your situation but still confused with your condition

Hi Azumi,

Thanks for replying.
Which condition are you confused about?

Hmm.. the point of it all is to determine which ID should be in Group 1 or Group 2, based on
their usage in the countries.

If the ID's usage is in majority (im just loosely saying this) countries belonging to Group 1 AND Usage above 300 AND are above revenue $450 but less than $600 then that ID belongs in Group 1.

Does this help at all??

Ok workin on it

Originally Posted by azumi

Ok workin on it

thanks
Hmm.. i've adjusted conditions, perhaps this is better..

Group 1 = (Count of ID in Group 1 countries> Count of ID in Group 2 countreis, Total Revenue for ID> 450>600, Usage per country> 300, Total Revenue per country >100, Frequency of ID in Country>2)

Group 2 = (Count of ID in Group 1 countries< Count of ID in Group 2 countreis, Total Revenue for ID>600, Usage per country> 300, Total Revenue per country >100, Frequency of ID in Country>2)

Originally Posted by xcelnoob77

Hi Azumi,

Thanks for replying.
Which condition are you confused about?

Hmm.. the point of it all is to determine which ID should be in Group 1 or Group 2, based on
their usage in the countries.

If the ID's usage is in majority (im just loosely saying this) countries belonging to Group 1 AND Usage above 300 AND are above revenue $450 but less than $600 then that ID belongs in Group 1.

Does this help at all??

Im used this condition, my head spnning you know hahaha

Originally Posted by azumi

Im used this condition, my head spnning you know hahaha

my head has fallen off my shoulders..

i think i will need overnight to process this.. will get back to u tomorrow lol... Thanks a million!

Okay anytime....

cheers

Originally Posted by azumi

Okay anytime....

cheers

I went back and tried to understand the formulas but I'm not so advanced skilled.
I can use what you did up for me as a verification, very useful however I have done up another spreadsheet.

Refer attached working_v2 sheet and tables sheet..

The examples were manually calculated of course since I dont know how to create formulas for that ;( but thats basically the desired result

Ok Im tryin again...... be patient

Im done with highlited cells, please check the file

Azumi

Originally Posted by azumi

Im done with highlited cells, please check the file

Azumi

Yes! you're a genius

Can I ask why do you use TRIM? Thanks.

Originally Posted by xcelnoob77

Yes! you're a genius

Can I ask why do you use TRIM? Thanks.

i am trying to figure out a formula for column H, but it doesnt work..
=IF(G2>VLOOKUP(working_v2!I2,tables!M2:O99,3,FALSE),"Yes","No")

Oh yes Im forgot to tell you, Im add up helper column (Column G) in Raw Sheet for Group, for reduce too complicated for formula, because you have separate tables for group

Originally Posted by azumi

Oh yes Im forgot to tell you, Im add up helper column (Column G) in Raw Sheet for Group, for reduce too complicated for formula, because you have separate tables for group

Yes, I saw the extra column. thanks...
i managed to get a formula for working_v2 sheet column H,
=IF(G2>INDEX(tables!$M$2:$O$132,MATCH($I2,tables!$N$2:$N$132,0),3),"Yes","No")

it seems to work fine.

Column I and J, i am trying too and patiently waiting for u

Originally Posted by azumi

Oh yes Im forgot to tell you, Im add up helper column (Column G) in Raw Sheet for Group, for reduce too complicated for formula, because you have separate tables for group

Hi Azumi,

I just realised there is a mistake in the formula on Column E..
For eg. ID 81637650
Col E should be India, not Malaysia. Can you check it?

Also, I cant seem to copy and paste all the formulas into another spreadsheet.. the formula doesnt work anymore..
See attached..

Fine Im checking again....

In my file its workin......

Hi Azumi,

Mine doesn't show it corretly.. i wonder why..

Check out the pic i attached.. 816347650, column E should be India not Malaysia.

xcelpic.png

Originally Posted by azumi

In my file its workin......

Also, Column F does not populate the correct count. For e.g 82866990 Column E is correct but Column F should be 3 instead of 2.

Add more TRIM to clean your ID criteria........

and now try to thinking to choose Group 1 or Group 2 Am I right? maybe tomorrow?

Originally Posted by azumi

and now try to thinking to choose Group 1 or Group 2 Am I right? maybe tomorrow?

Sure thing..tomorrow or sunday or monday.. thanks.
However Col E and F still does not fetch the correct value.. I am trying to correct that..

Originally Posted by azumi

and now try to thinking to choose Group 1 or Group 2 Am I right? maybe tomorrow?

Hi Azumi,

I am using a different formula for my column B and C..

=SUMPRODUCT(IF(FREQUENCY(IFERROR(MATCH(IF($A3=Table1[ID],Table1[Country]),Group1,0),"e"),IFERROR(MATCH(IF($A3=Table1[ID],Table1[Country]),Group1,0),"e")),1,0))

But it is not tabulating correctly.. do you know how to correct this?