Pull ranking & associated data - NON VBA - Possible?????

spydey

Active Member
Joined
Sep 19, 2017
Messages
293
Morning y'all!

I am trying to figure out how I can pull some data over in a specific way to ensure that it matches my criteria.

I have my source workbook which contains all data for Item, Location, Ranking (based upon location), & Data Points 1-10 (1 through 10).

I also have my template sheet (separate workbook) where I will present the data per Item based upon location, but I need to do a quick comparison of up to 2 other items, based upon ranking, for the same location as the primary item. I also need to present the corresponding data points 1 - 10 for the primary Item and the comparison items.

I need to present data on each item, example: Item #1 in Location #2 . I can present the Data Points 1-10 for that item, easy, via an Index(Match)) array referencing the Source workbook. What I am having difficulty with is grabbing the correct comparison items for the same location as the primary item, based upon ranking.

Here is an example of my template:

ABCDEFGHIJKLMN
1Primary Item Name:XYZ
2Location:AB
3ItemsRankDP 1DP 2DP 3DP 4DP 5DP 6DP 7DP 8DP 9DP 10
4Primary Item1
5Item 92
6Item 53
7

<tbody>
</tbody>

If the Primary Item is ranked 1st, then I need to present the 2nd and 3rd ranked items', for the same location as the primary item, ranking & corresponding Data Points 1 - 10.

If the Primary Item is ranked 2nd, then I need to present the 1st and 3rd ranked items', for the same location as the primary item, ranking & corresponding Data Points.

If the Primary Item is ranked >= 3rd, then I need to present the 1st and 2nd ranked items', for the same location as the primary item, ranking & corresponding Data Points.

One other requirement is that I cannot show the other ranked Items' "Name" (i.e. Item 9, Item 2, Item 3, Item X) when they are the comparison items.

Also, there may be more than 3 items ranked for the same location, but I only need to know about the primary item vs the top two highest ranked items for the same location as the primary item.

Also, there may only be the primary item in the location in which case there wouldn't be anything to compare to, in which case I need to leave the other two comparison item rows blank.

I have up to about 90 locations, some with only 1 item, and some with 10+ items.

Also, the same Item can be in multiple locations but each template will only cover the primary item in a single location.

I hope that is clear enough.

So I was thinking of using the following, but am not entirely sure on how to compile it all into a single formula, if possible. I am trying to break it down into simple parts to understand and formulate and then combine them.

-------

I need to carry over the ranking for the primary item from my Source Sheet.

Code:
=Index(SourceRange, Match(1,(ItemColumnInSourceRange=PrimaryItemName)*(LocationColumnInSourceRange=PrimaryItemLocation),0),RankingColumnNumberInSourceRange)
I can use a similar formula to find the Data Points 1-10 for the Primary Item and place them in the corresponding column for the Primary Item by just changing the ColumnNumberInSourceRange to the needed column number for each DP 1-10.

-------

I need to determine the number of items per location; this will help me know how many spots to fill, up to two, for the comparison items, this will be used in conjunction with other formulas to brink over the ranking for the comparison items.

Code:
=Countif(LocationRangeInSource,Location)
-------

I need to find the corresponding ranking for the comparison items based upon location of primary item and ranking of primary item, following the criteria previously mentioned above. This is the tricky part for me. I can see what I want and understand what I want, but putting it in to a logical formula is what is getting me. I think that I will need a separate formula for each comparison item, and change it slightly so that I cover my criteria.

For comparison Item #1 :
Code:
=If(Countif(LocationRangeInSource,Location)<2,"",If(PrimaryItemRanking=1,2,If(PrimaryItemRanking>1,1)))
For comparison Item #2 :
Code:
=If(Countif(LocationRangeInSource,Location)<3,"",If(PrimaryItemRank=1,3,If(PrimaryItemRanking=2,3,If(PrimaryItemRanking>2,2))))
-------

I need to now find the corresponding Data Points 1-10 for the comparison items, based on the location & ranking. This one too is getting me a bit but I think this might work:

Code:
=If(ComparisonItemRanking="","",Index(SourceRange,Match(1,(LocationColumnInSourceRange=PrimaryItemLocation)*(RankingColumnInSourceRange=ComparisonItemRanking),0),DataPointColumnNumberInSourceRange))
-------

I think that if I put all that together, it might work but I am not even really sure at this point. I am going to give it a try but was hoping that I might be able to get some input on if this is the best approach at doing what I need.

Also, I think that writing it out like this really helped me somewhat visualize it and articulate my thoughts better.

What do you think? Will this work for what I am trying to do?

-Spydey
 
Last edited:

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,985
I suggest you make up a small pretend sheet and tell us what you want to do with the data in terms of desired output tables - could be just item,location, score (which is what I assume is to be ranked)
 

spydey

Active Member
Joined
Sep 19, 2017
Messages
293
I suggest you make up a small pretend sheet and tell us what you want to do with the data in terms of desired output tables - could be just item,location, score (which is what I assume is to be ranked)

Thanks Oldbrewer, I might do that. I actually was able to get the majority of it to work as expected. The only issue that I am having now is that my Index(Match)) array is not pulling the correct data over. It is either giving me the data for one row up or one row down, from the needed data. Also, the output is changing as I sort & filter the source sheet, which shouldn't happen.

I am going to look into it a bit more and hopefully post something back. :D

-Spydey
 
Last edited:

spydey

Active Member
Joined
Sep 19, 2017
Messages
293
Got it!!!

I just needed to adapt my final Index(Match)) array a bit and now it is gathering the correct information and not changing as I sort and filter the source data.

-Spydey
 

Forum statistics

Threads
1,082,343
Messages
5,364,790
Members
400,815
Latest member
gangstar67

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top