Solved

return multiple instance of a value in a rw

Posted on 2011-03-10
13
226 Views
Last Modified: 2012-05-11
Hi experts,

I have an excel pivot table with 400+ columns designating a "storage bin location"
There are 1200+ rows designating unique "Item #'s"
The pivot table values designate qty stored in each bin location
An item # may appear in multiple bin locations in the pivot table

I need a formula or method for returning the bin location for the 1st instance of a qty >0, then return the bin location of 2nd instance where qty>0, then 3rd...to n possible locations. Upper limit of n likely can be assumed less than 20 possible locations.

I was thinking adding 20 columns at the end of the pivot table and using a hlookup to find qty values >0 and return bin location on top row of pivot table. Problem is telling column 2 to return the bin location of the 2nd instance in the row, then column 3 to return 3rd instance, etc.

Any help greatly appreciated.

Kevin

0
Comment
Question by:KevinHatt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
13 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35095900
For example:
=INDEX(1:1,SMALL(IF(3:3>0,COLUMN(3:3)),2))
array-entered will return the value from row 1 for the second value greater than zero in row 3. Is that what you meant?
0
 

Author Comment

by:KevinHatt
ID: 35096002
Hi rorya,

I dont think so...

example:
                   [ Col1-Col2- Col3- Col4...Col400]    [Location1     Location2]...
item 123          4                5                                  Col1                Col3

does that make sense?

0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35096023
Yes, that's basically what the formula I posted does. If you just want the column number (rather than the contents of row 1, then use:
=SMALL(IF(3:3>0,COLUMN(3:3)),1) for the first item
=SMALL(IF(3:3>0,COLUMN(3:3)),2) for the second item
etc.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:KevinHatt
ID: 35097628
apologies for being obtuse Rorya...I think you are correct, I'm just fighting syntax here. Can I ask for some specific syntax for the given data range?

Row that contains Bin location is row 4
qty values are located in range b5 to pc5

Also...when entering the array range, is it necessary to ctrl+shift+enter?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35100352
Yes it must be ctrl+ shift+ enter
Basically use
=INDEX(4:4,SMALL(IF(b4:pc4>0,COLUMN(b4:pc4)),2))
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35100365
Yes it must be ctrl+ shift+ enter
Basically use
=INDEX(4:4,SMALL(IF(b5:pc5>0,COLUMN(b5:pc5)),2)
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 35107154
Correction:
=INDEX(B4:PC4,SMALL(IF(b4:pc4>0,COLUMN(b4:pc4)),2))
still array-entered.
0
 

Author Comment

by:KevinHatt
ID: 35114613
Hi rorya,

Your help was most gratefully received Master Savant. Formulas working as they should,aand client happy.

Full points to be awarded, and thanks again!

Kevin
0
 

Author Comment

by:KevinHatt
ID: 35114616
closed
0
 

Author Comment

by:KevinHatt
ID: 35114619
closed
0
 

Author Comment

by:KevinHatt
ID: 35114628
closed
0
 
LVL 24

Expert Comment

by:broomee9
ID: 35357007
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question