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

[spydey]

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:

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

Primary Item Name:

XYZ

2

Location:

AB

3

Items

Rank

DP 1

DP 2

DP 3

DP 4

DP 5

DP 6

DP 7

DP 8

DP 9

DP 10

4

Primary Item

1

5

Item 9

2

6

Item 5

3

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.

=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.

=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 :

=If(Countif(LocationRangeInSource,Location)<2,"",If(PrimaryItemRanking=1,2,If(PrimaryItemRanking>1,1)))

For comparison Item #2 :

=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:

=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

 

[oldbrewer]

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]

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

 

[spydey]

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