May 23 2019 04:15 AM
Hello,
=IFERROR(INDEX(Input!C:C,MATCH(0,IF($I$2=Input!A:A,COUNTIF($I$2:I3,Input!C:C),""),0)),"")
I am using this array formula to sort a list of many different serial numbers into a list containing one serial number of each. I would then also like to extract the serial numbers containing certain letters. I thought about using the following formula, =ISNUMBER(SEARCH(substring,text), but can't figure out where within the initial formula to place it.
Many thanks.
May 23 2019 09:05 AM
May 24 2019 02:40 AM
Find file attached. TY
May 24 2019 03:31 AM
May 24 2019 06:04 AM - edited May 24 2019 06:38 AM
SolutionMay 24 2019 06:44 AM
Much appreciated attempt but the list in F are all the same serial numbers, not individual.
May 24 2019 06:50 AM
Please ignore my previous post. Your formula works perfectly. I am not familiar with the use of aggregate but I will research it's functions to see how I can use them further.
Thank you for your responses.
May 24 2019 06:56 AM
May 28 2019 08:52 AM
The formula works if I use data from the same worksheet but I tried to use the formula across different tabs and using columns/tables but it is not working. Would anything need to change for it to work?
May 28 2019 09:07 AM
May 29 2019 01:29 AM
See attached revised file. Formula in column H on Sheet1 has been modified to include Sheet2 however the results are inconsistent, duplicated and, depending on which "Town" is entered into Sheet1 H2, may not even display an "AB" serial number. Many thanks for your assistance.
Jan 31 2020 10:09 AM
May 24 2019 06:04 AM - edited May 24 2019 06:38 AM
Solution