SOLVED
Home

extract specific data from a list

%3CLINGO-SUB%20id%3D%22lingo-sub-637090%22%20slang%3D%22en-US%22%3Eextract%20specific%20data%20from%20a%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-637090%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%3DIFERROR(INDEX(Input!C%3AC%2CMATCH(0%2CIF(%24I%242%3DInput!A%3AA%2CCOUNTIF(%24I%242%3AI3%2CInput!C%3AC)%2C%22%22)%2C0))%2C%22%22)%3C%2FP%3E%3CP%3EI%20am%20using%20this%20array%20formula%20to%20sort%20a%20list%20of%20many%20different%20serial%20numbers%20into%20a%20list%20containing%20one%20serial%20number%20of%20each.%20I%20would%20then%20also%20like%20to%20extract%20the%20serial%20numbers%20containing%20certain%20letters.%20I%20thought%20about%20using%20the%20following%20formula%2C%20%3DISNUMBER(SEARCH(substring%2Ctext)%2C%20but%20can't%20figure%20out%20where%20within%20the%20initial%20formula%20to%20place%20it.%3C%2FP%3E%3CP%3EMany%20thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-637090%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-638268%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20specific%20data%20from%20a%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-638268%22%20slang%3D%22en-US%22%3ETo%20enable%20formula%20testing%2C%20please%20attach%20your%20sample%20file.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-643346%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20specific%20data%20from%20a%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-643346%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFind%20file%20attached.%20TY%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-643428%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20specific%20data%20from%20a%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-643428%22%20slang%3D%22en-US%22%3EPlease%20manually%20enter%20your%20desired%20results%20in%20Column%20F.%20Then%2C%20attach%20your%20sample%20file%20again.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-643430%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20specific%20data%20from%20a%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-643430%22%20slang%3D%22en-US%22%3EPlease%20manually%20enter%20your%20desired%20results%20in%20Column%20F.%20Then%2C%20attach%20your%20sample%20file%20again.%20I%20will%20work%20for%20the%20formula%20from%20there.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-643549%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20specific%20data%20from%20a%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-643549%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-643677%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20specific%20data%20from%20a%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-643677%22%20slang%3D%22en-US%22%3EI%20am%20replying%20via%20mobile%20phone%20so%20I%20can%E2%80%99t%20test%20this%20formula%20in%20D3%2C%20copied%20down%20rows%3A%3CBR%20%2F%3E%3DIFERROR(INDEX(B%243%3AB%24100%2C%3CBR%20%2F%3EAGGREGATE(15%2C6%2C%3CBR%20%2F%3E1%2F((A%243%3AA%24100%3DD%242)*%3CBR%20%2F%3E(LEFT(B%243%3AB%24100%2C2)%3DD%241)*%3CBR%20%2F%3E(COUNTIF(D%242%3AD2%2CB%243%3AB%24100)%3D0))*%3CBR%20%2F%3E(ROW(A%243%3AA%24100)-2)%2C1))%2C%E2%80%9D%E2%80%9D)%3CBR%20%2F%3EThe%20foregoing%20formula%20assumes%20that%20the%20text%20%E2%80%9CAB%E2%80%9D%20is%20stored%20in%20D1%20to%20avoid%20hard-coding%20thereof.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-643995%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20specific%20data%20from%20a%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-643995%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMuch%20appreciated%20attempt%20but%20the%20list%20in%20F%20are%20all%20the%20same%20serial%20numbers%2C%20not%20individual.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644022%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20specific%20data%20from%20a%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644022%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20ignore%20my%20previous%20post.%20Your%20formula%20works%20perfectly.%20I%20am%20not%20familiar%20with%20the%20use%20of%20aggregate%20but%20I%20will%20research%20it's%20functions%20to%20see%20how%20I%20can%20use%20them%20further.%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20responses.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644033%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20specific%20data%20from%20a%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644033%22%20slang%3D%22en-US%22%3EYou%E2%80%99re%20very%20much%20welcome.%20To%20access%20help%20on%20the%20function%2C%20select%20the%20cell%2C%20press%20F2%2C%20and%20click%20the%20function%20name.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-652620%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20specific%20data%20from%20a%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-652620%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20works%20if%20I%20use%20data%20from%20the%20same%20worksheet%20but%20I%20tried%20to%20use%20the%20formula%20across%20different%20tabs%20and%20using%20columns%2Ftables%20but%20it%20is%20not%20working.%20Would%20anything%20need%20to%20change%20for%20it%20to%20work%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-652655%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20specific%20data%20from%20a%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-652655%22%20slang%3D%22en-US%22%3EFor%20the%20formula%20to%20work%20in%20other%20sheets%2C%20you%20must%20accordingly%20modify%20the%20references.%20Nonetheless%2C%20the%20logic%20shall%20remain%20the%20same.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-654643%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20specific%20data%20from%20a%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-654643%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20attached%20revised%20file.%20Formula%20in%20column%20H%20on%20Sheet1%20has%20been%20modified%20to%20include%20Sheet2%20however%20the%20results%20are%20inconsistent%2C%20duplicated%20and%2C%20depending%20on%20which%20%22Town%22%20is%20entered%20into%20Sheet1%20H2%2C%20may%20not%20even%20display%20an%20%22AB%22%20serial%20number.%20Many%20thanks%20for%20your%20assistance.%3C%2FP%3E%3C%2FLINGO-BODY%3E
pacificstorm82
Occasional Contributor

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.

11 Replies
To enable formula testing, please attach your sample file.

@Twifoo 

Find file attached. TY

Please manually enter your desired results in Column F. Then, attach your sample file again. I will work for the formula from there.
Solution
I am replying via mobile phone so I can’t test this formula in D3, copied down rows:
=IFERROR(INDEX(B$3:B$100,
AGGREGATE(15,6,
1/((A$3:A$100=D$2)*
(LEFT(B$3:B$100,2)=D$1)*
(COUNTIF(D$2:D2,B$3:B$100)=0))*
(ROW(A$3:A$100)-2),1)),””)
The foregoing formula assumes that the text “AB” is stored in D1 to avoid hard-coding thereof.

@Twifoo 

Much appreciated attempt but the list in F are all the same serial numbers, not individual.

@Twifoo 

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.

You’re very much welcome. To access help on the function, select the cell, press F2, and click the function name.

@Twifoo

 

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?

For the formula to work in other sheets, you must accordingly modify the references. Nonetheless, the logic shall remain the same.

@Twifoo 

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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies