• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Use of large, index match, or VBA?

J

jasonleewkd

New Member
Hi gurus at Chandoo.


Can anyone help me with the dataset I have and the retrieval?


I have used formulas large, index & match, but not vba as I'm not well-versed in it.


On the Y axis are nationalities.

On the X axis are residential districts.

The number in the body of the data is the buyer nationality of a certain house in a district.

Problem: I need a quick way to find the 3 largest buyers in each district. I've used large, index & match.


The file can be downloaded here.


https://docs.google.com/open?id=0BzQyFnW9qIEXVGU2N2VtVHJ6RkE


I will be eternally grateful to any guru who can help me with this! Cheers, Jason
 
[pre]
Code: 
District 1
Australia	   10
Singapore	   9
China	           9
USA	           9
Germany	           6
Malaysia	   5

The above turns into the below (using various formulas, large, index/match)

Name of top 3 in district 1	Top 3 in district 1
Australia	                  10
Singapore	                  9
Singapore	                  9

The top districts are Australia, Singapore, China, USA with 10,9,9,9.


The table shows Australia at 10, followed by Singapore and Singapore. 


Is there a method to show is by alphabetical order, i.e. Top 3 are Australia, China, then Singapore.

[pre][code]Name of top 3 in district 1	Top 3 in district 1
Australia	                  10
China                             9
Singapore	                  9
[/pre]

Alternatively, is there a way to show the top 3 as


Name of top 3 in district 1 Top 3 in district 1
Australia 10
China, Singapore, USA 9
Germany 9[/code][/pre]
I have attached the excel file in google docs. https://docs.google.com/open?id=0BzQyFnW9qIEXSG9zR0lGUVJNMnM


Thank you for all your kindness sirs.
 
Hi Jason ,


Have you checked out the file I posted yesterday ? It does not show the nationalities in alphabetical order ; if this is a requirement , then it can certainly be done by using a helper column.


Check out Sheet2.


Narayan
 
Narayan,


Yes I have checked out sheet 2. I've learnt the offset function from Chandoo's website and it is very helpful. I've implemented it in my other uses of excel. I understand that your formula has expanded to include indirect, large, and the use of named ranges. Very helpful.


If you see on Sheet 1, the problem exists as I've described in the text box. Could you elaborate on how the helper column could be implemented?


Is there a method also to show?

[pre]
Code: 
Name of top 3 in district 1	Top 3 in district 1
Australia	                  10
China, Singapore, USA             9
Germany	                          9
[/pre]

Thanks so much.
 
Hi Jason ,


Check out this file :


https://docs.google.com/open?id=0B0KMpuzr3MTVSDRkMDJxYkxKWWs


I think the concatenated string of sorted nationalities will be more difficult.


Narayan
 
Hi Chandoo ninjas, would like to seek your help. I've looked through the above solutions and it's great.


However, there's a tweak I'd like to make. If you look into the attached spreadsheet, is there a way to return (for example) the top buyer nationalities without the use of named ranges?


The excel doc can be downloaded here :

https://docs.google.com/open?id=0BzQyFnW9qIEXY1JKSEFGdHJlX00


Extra info are in cells A1, AN1, AN9, BC9 :)
 
Good day jasonleewkd

If you turned your separate lists of data into tables you could select what you wanted to view, a better way is to make a pivot table of each section that way you can slice, dice and filter any which way you want
 
Hi bob, the data comes from a third party provider and I would love to turn it into a table, but that would be a lot of manual work. Would you have any idea on how to turn it into a table?
 
Hi jasonleewkd


I downloaded you file and turned the first three data sets into tables with a couple of clicks, I then picked one set of data and turned into a pivot table in a few seconds.This link may help you


http://chandoo.org/wp/2009/09/10/data-tables/
 
Hi bob, thanks for your assistance but I didn't explain myself clearly and caused you to misunderstand. I've actually kind of found the answer to my question and it can be found using


AO4 down:

Confirm Control+Shift+Enter

=IFERROR(INDEX($A$4:$A$47,SMALL(IF(B$4:B$47=AP4,ROW($B$4:$B$47)-ROW($AO$4)+1),COUNTIF(AP$4:AP4,AP4))),"")

AP4 down

=IFERROR(LARGE(B$4:B$46,$AN4),"")


Thank you nonetheless
 
You must log in or register to reply here.
Back
Top