Two column data and query on another worksheet - Sumproduct?

[WO]

Hi folks

Hope you can help - sorry for my description above but im not sure what to call it. Looked through various topics prvious but cant seem to find the solution. Im thinking its a SUMPRODUCT dilema?

One cell (worksheet1/cell a2/b2/b3)

Worksheet 1

Row A : Dubai

Row B: Barbados

Row C : Antigua

is asking the question of another worksheet (worksheet2)which has


Worksheet2

ColumnA - Destination

A1: Dubai

A2: Barbados

A3: Dubai

A4: Dubai

A5: Barbados

A6: Antigua

A7: Dubai

Randome based on previous excel query result


ColumnB - Hotel

A1: Burj

A2: Sandy Lane

A3: Burj

A4: Palm

A5: Beach

A6: Bay

A7: Burj


I want to find the most popular hotel to fit into the query raised by Worksheet1 a2/b2/c2 which is asking which hotel is both the most popular from Column B but also matches Column A on both worksheets

Answers should be

Worksheet 1

Column A2 : Burj (Top Dubai Hotel)

B2 : Sandy Lane / Beach (Spilt) (Top Barbados Hotel)

C2 : Bay (Top Antigua Hotel)

However the data is obvioulsy a lot more rows so it must add up all the 'Dubai' hotels first and pick the most frequent.

Can you help? Im sure it's 'simples' really?!

Thanks

 

[vasim]

May be you can use pivottable???

 

[WO]

Can i do this from within another table?? Sorry im an amateur at this!!

THanks

 

[WO]

Sorry i know how to do Pivot Tables but i want it to only add up the most popular hotel in a resort. A pivot will just give me a whole raft of answers, will it not?

 

[WO]

ive uploaded the file here

https://rapidshare.com/files/2298294832/Copy_of_Final_Dashboard.xlsx

Beaches & Retreats Tab Column Y (Top Hotel)

By analysising data in Beaches Data Tab Column T & U

Anyone help?

THanks a mill

Andrew

 

[NARAYANK991]

Hi Andrew ,


You mention "by analysing data in the Beaches Tab columns T and U" ; however column U is labelled "Client Status" , shouldn't the actual column be column AB , which contains the hotel name ?


Narayan

 

[WO]

Hi Narayank991

It's Column U on the sheet im looking at, but yes its the 'Hotel' name i need to return. The most popular one.

Thanks

 

[WO]

I know nothing about VBA, other than reading various Chandoo blogs...maybe VBA is my solution here? Anyone??

 

[NARAYANK991]

Hi Andrew ,


Can you give me some time , I'll let you have the solution. It need not involve VBA , though using VBA makes it much easier.


Narayan

 

[NARAYANK991]

Hi Andrew ,


Sorry for the delay. Can you check out the following link ?


https://skydrive.live.com/#!/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21136


Please note that clicking on the link will not do ; please copy the entire address and paste it in your browser.


There are 3 named ranges , Resorts , Hotels and Popularity ; 4 helper columns.


If I can get it without any helper columns , I'll post the solution here ; let me know whether this does the job correctly.


Narayan

 

[WO]

Hi Narayank

This looks like the perfect solution, i really do appreciate your genius!

I know it looks like a long route to solve what possibly should be quite a simple query, but this is really great, thanks again.

I wonder if Excel would build a 'most popular text' string in the future?

Cheers

Andrew

 

[WO]

Hi Narayank

Me again! Ive actually put this data into a 'real' spreadsheet example and im not sure its working to be honest.

I think the Maximum Count Column isnt correct. =MAX((Resorts=T5)*($AD$2:$AD$100))


Zoetry 1 1 1 Zoetry (Mexico)

Chedi 1 1 2 Chedi (Oman)

Ubud 1 1 3 Ubud (Bali)

Conse 1 3 4 Conse (Maldives)

Coco 3 3 4 Conse (Maldives)

The Coco is actually the most popular hotel in Maldives as displayed by 'Popularity and Maximum Count' so i think the Maximum Count is incorrect for Conse as it appears to be counting the resort perhaps?? (There is more data which this is looking at not just these 5 sample rows).

Do you think you could have another look please?

Do you want me to post the 'live' spreadsheet again?

Thanks in anticpation

Andrew

 

[WO]

hang on i may have got the data wrong when i copied!!!! Sorry!

I did get it wrong...it works fine!!!!

THANKS!!!

 

[NARAYANK991]

Hi Andrew ,


Thanks. And in case you find any mistake in the formulae , I am always available to correct ! No problem.


Narayan