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

Using Index/Match/Min to output a value

P

peter short

New Member
Hi,

Thanks for helping out if you can.
I have a large data set with items laid out as such going down many thousands of rows
ID Item Name Manuf No Len Count of Item Name
61379672 PIE990124446-au 990124446 15
61329790 PIELAR228M4-000-cc LAR228M4 18
61329691 PIEFSP228P9A4-003-ed FSP228P9A4 20
61329745 ABB64102931-fe 990124446 14
61329801 MOOFM2P128PBA4-dd FM2P128PBA4 17


I have run a pivot table to make a list of duplicate manufacturer numbers in the dataset from which I want to output an ID in the next column that correpsonds to the manuf No that has the lowest len count from the manufacturer name.

The duplicate output list looks like this for which I want to populate the ID column with the lowest len count ID for a particular Manuf No (which requires comparing across arrays).

Man No ID
990124446 61329745
10003 need
10017 need
10018 need
10020 need
10023 need
10024 need
10025 need

THe formula I have written works in isolation

=INDEX($A$1:$A$32942,MATCH(1,(Output!A2=$C$1:$C$32942)*(MIN(IF($D$1:$D$32942=Output!A2,$D$1:$D$32942))=$D$1:$D$32942)),0), however is failing to populate as intended when I drag down.

I have never worked with ctrl+shift+enter formulas before

Does anyone have any ways to write a formula to achieve this task in as few steps as possible without writing a macro?

Many thanks,

Peter
 
Hi Narayan,

I have uploaded a sample workbook!
I am needing to populate the highlighted column with an ID based on a match of the manuf number for which the lens has the lowest or min value - if this makes sense!

Many thanks!

Peter
 

Attachments

Hi Peter ,

See the file.

Since the minimum length is already available in the helper column ( column L ) , the formula need not again have MIN in it ; it can just use the value that is available in column L.

Narayan
 

Attachments

Narayan,

That's brilliant - I've never seen the "|" before - which is a great way to solve this problem!
That's a great result and I really appreciate you helping out there.

Many thanks again,


Peter
 
Hi Peter ,

The "|" symbol is used when concatenating two items of data , so that we do not have unintended matches ; for example if we concatenate 22 & 785 , to get 22785 , this may well match with 2 cells which have 2 and 2785 in them. If the "|" symbol is used , then we have the string 22|785 , which will match only with another 22 and 785 , and not with 2 and 2785 , since the latter would result in 2|2785.

Narayan
 
Thanks for talking me thru that Narayan, it's a really cool tool to have. By the way, do you happen to know if there is a way to drag the formulas down and apply the array ctrol+shift+enter function without having to do each cell individually?

Kind Regards,

Peter
 
Hi Peter ,

I am not sure I have understood your question ; I array entered the formula in K6 and then dragged it down. There should not be any need to go to each cell and individually array-enter it.

Narayan
 
Thanks Narayan,

No problems, had some issues with the setting on the formula automatic update function.
All solved now. I really appreciate your time and expertise.

Many thanks,

Peter
 
You must log in or register to reply here.
Back
Top