SOLVED
Home

In desperate need of a formula for a -basically- simple task

%3CLINGO-SUB%20id%3D%22lingo-sub-843558%22%20slang%3D%22en-US%22%3EIn%20desperate%20need%20of%20a%20formula%20for%20a%20-basically-%20simple%20task%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-843558%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20building%20a%20test%20file%20in%20which%20to%20store%20test%20results%20but%20I%20am%20at%20a%20total%20loss.%20This%20is%20my%20problem%3A%26nbsp%3B%3C%2FP%3E%3CP%3E-%20Our%20products%20are%20sold%20in%20combination%20with%201%2C%202%20or%203%20accessories.%3CBR%20%2F%3E-%20Products%20are%20manufactured%20on%20demand%2C%20and%20from%20every%20batch%20a%20sample%20needs%20to%20be%20tested.%3CBR%20%2F%3E-%20We%20test%20the%20samples%20with%20only%201%20of%20the%20possible%20accessories%20at%20a%20time%3A%20a%20matter%20of%20cost%20and%20time.%3CBR%20%2F%3E-%20We%20first%20test%20product%201%20with%20accessory%201.%20The%20next%20time%20product%201%20needs%20to%20be%20tested%2C%20we%20do%20it%20with%20accessory%202%2C%20the%20third%20time%20with%20accessory%203%20and%20the%20fourth%20time%20with%20accessory%201%20again.%20The%20same%20goes%20for%20all%20of%20our%20products.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhich%20formula%20will%20return%20the%20right%20accessory%20to%20test%20with%20next%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-843558%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%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-843625%22%20slang%3D%22en-US%22%3ERe%3A%20In%20desperate%20need%20of%20a%20formula%20for%20a%20-basically-%20simple%20task%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-843625%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F405726%22%20target%3D%22_blank%22%3E%40Blogpapa%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20try%20this%20and%20see%20if%20this%20is%20what%20you%20were%20trying%20to%20achieve...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20H3%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DINDEX(Table1%5B%5Baccessory%201%5D%3A%5Baccessory%203%5D%5D%2CMATCH(%5B%40product%5D%2CTable1%5Bproduct%5D%2C0)%2CCOUNTIF(F%243%3AF3%2CF3))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-846251%22%20slang%3D%22en-US%22%3ERe%3A%20In%20desperate%20need%20of%20a%20formula%20for%20a%20-basically-%20simple%20task%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-846251%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3BThis%20seems%20to%20be%20working%20%3A)I%20still%20have%20to%20translate%20it%20to%20the%20actual%20file%20but%20if%20this%20works%2C%20you%20are%20truly%20a%20life%20saver!%20Thank%20you%20so%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-846288%22%20slang%3D%22en-US%22%3ERe%3A%20In%20desperate%20need%20of%20a%20formula%20for%20a%20-basically-%20simple%20task%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-846288%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3BIt%20sounded%20too%20good%20to%20be%20true...%20It%20works%20perfectly%20for%20the%20first%20part%2C%20but%20two%20things%20are%20not%20working%20yet%3A%3C%2FP%3E%3CP%3E-%20after%20the%203%20accessory%20options%20are%20tested%2C%20the%20fourth%20test%20should%20use%20accessory%201%20again%2C%20the%20fifth%20test%20should%20use%20accessory%203%20again%2C%20et%20cetera.%20So%2C%20a%20continuous%20loop.%3C%2FP%3E%3CP%3E-%20products%20that%20have%20only%202%20test%20accessories%20should%20cycle%20through%20those%202%20(1-2-1-2-1-2%20instead%20of%201-2-0-1-2-0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThink%20you%20can%20figure%20that%20out%3F%20That%20would%20be%20amazing%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-846386%22%20slang%3D%22en-US%22%3ERe%3A%20In%20desperate%20need%20of%20a%20formula%20for%20a%20-basically-%20simple%20task%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-846386%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F405726%22%20target%3D%22_blank%22%3E%40Blogpapa%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20tweak%20your%20master%20data%20to%20fill%20the%20empty%20cells%20for%20any%20product%2C%20if%20any%2C%20manually%2C%20the%20following%20Array%20Formula%20will%20return%20the%20desired%20output.%3C%2FP%3E%3CP%3Ee.g.%20Cell%20D5%20in%20your%20master%20data%20is%20empty%20which%20you%20can%20fill%20manually%20with%20the%20first%20occurrence%20of%20the%20accessory%20which%20is%20'a'%20in%20this%20case.%20I%20have%20filled%20it%20manually%20in%20the%20master%20data.%3C%2FP%3E%3CP%3ESee%20if%20you%20can%20tweak%20the%20master%20data%20manually%20for%20those%20empty%20cells%20if%20any.%20If%20yes%2C%20you%20can%20try%20the%20following%20%3CSTRONG%3EArray%20Formula%3C%2FSTRONG%3Ewhich%20requires%20confirmation%20with%20%3CSTRONG%3ECtrl%2BShift%2BEnter%3C%2FSTRONG%3Einstead%20Enter%20alone...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20H3%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(INDEX(Table1%5B%5Baccessory%201%5D%3A%5Baccessory%203%5D%5D%2CMATCH(%5B%40product%5D%2CTable1%5Bproduct%5D%2C0)%2CCOUNTIF(F%243%3AF3%2CF3))%2CINDEX(H%242%3AH2%2CSMALL(IF(F%242%3AF2%3D%5B%40product%5D%2CROW(F%242%3AF2)-ROW(%24F%242)%2B1)%2CIF(MOD(COUNTIF(F%242%3AF2%2C%5B%40product%5D)%2CCOUNTA(INDEX(Table1%5B%5Baccessory%201%5D%3A%5Baccessory%203%5D%5D%2CMATCH(%5B%40product%5D%2CTable1%5Bproduct%5D%2C0)%2C)))%3D0%2C1%2CMOD(COUNTIF(F%242%3AF2%2C%5B%40product%5D)%2CCOUNTA(INDEX(Table1%5B%5Baccessory%201%5D%3A%5Baccessory%203%5D%5D%2CMATCH(%5B%40product%5D%2CTable1%5Bproduct%5D%2C0)%2C)))%2B1))))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThen%20confirm%20with%20%3CSTRONG%3ECtrl%2BShift%2BEnter%3C%2FSTRONG%3Einstead%20of%20Enter%20alone.%3C%2FP%3E%3CP%3EIf%20you%20have%20access%20to%20the%20dynamic%20array%20formula%2C%20you%20don't%20need%20the%20special%20key%20stroke%20Ctrl%2BShift%2BEnter%20to%20confirm%20an%20array%20formula%2C%20Enter%20is%20enough%20in%20that%20case.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-848141%22%20slang%3D%22en-US%22%3ERe%3A%20In%20desperate%20need%20of%20a%20formula%20for%20a%20-basically-%20simple%20task%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-848141%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe're%20almost%20there!%20The%20only%20thing%20is%3A%20with%20this%20formula%20product%203%20would%20be%20tested%20with%20accessories%20a-d-a-a-d-a-a-d-a-a-d-a%2C%20whil%20it%20should%20be%20a-d-a-d-a-d-a-d.%20This%20way%20I%20would%20test%20twice%20as%20much%20with%20'a'%20as%20with%20'd'.%20That%20would%20corrupt%20the%20outcome...%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-848247%22%20slang%3D%22en-US%22%3ERe%3A%20In%20desperate%20need%20of%20a%20formula%20for%20a%20-basically-%20simple%20task%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-848247%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F405726%22%20target%3D%22_blank%22%3E%40Blogpapa%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20find%20the%20attached%20and%20let%20me%20know%20if%20the%20formula%20is%20working%20as%20desired%20now.%3C%2FP%3E%3CP%3EI%20am%20going%20to%20post%20the%20formula%20in%20the%20next%20reply%20because%20last%20time%20when%20I%20posted%20the%20formula%2C%20somehow%20the%20site%20considered%20it%20as%20spam%20and%20didn't%20allow%20me%20to%20post%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-848248%22%20slang%3D%22en-US%22%3ERe%3A%20In%20desperate%20need%20of%20a%20formula%20for%20a%20-basically-%20simple%20task%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-848248%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F405726%22%20target%3D%22_blank%22%3E%40Blogpapa%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20tweaked%20formula...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DINDEX(Table1%5B%5Baccessory%201%5D%3A%5Baccessory%203%5D%5D%2CMATCH(%5B%40product%5D%2CTable1%5Bproduct%5D%2C0)%2CIF(MOD(COUNTIF(F%243%3AF3%2CF3)%2CCOUNTA(INDEX(Table1%5B%5Baccessory%201%5D%3A%5Baccessory%203%5D%5D%2CMATCH(%5B%40product%5D%2CTable1%5Bproduct%5D%2C0)%2C)))%3D0%2CCOUNTA(INDEX(Table1%5B%5Baccessory%201%5D%3A%5Baccessory%203%5D%5D%2CMATCH(%5B%40product%5D%2CTable1%5Bproduct%5D%2C0)%2C))%2CMOD(COUNTIF(F%243%3AF3%2CF3)%2CCOUNTA(INDEX(Table1%5B%5Baccessory%201%5D%3A%5Baccessory%203%5D%5D%2CMATCH(%5B%40product%5D%2CTable1%5Bproduct%5D%2C0)%2C)))))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-848289%22%20slang%3D%22en-US%22%3ERe%3A%20In%20desperate%20need%20of%20a%20formula%20for%20a%20-basically-%20simple%20task%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-848289%22%20slang%3D%22en-US%22%3E%3CP%3EHoly%20crap%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20looks%20fantastic!%20%3A)I%20will%20build%20it%20in%20my%20masterfile%20and%20post%20the%20outcome%20here.%20This%20is%20really%20great%20stuff%2C%20thanks%20a%20lot!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%2C%3C%2FP%3E%3CP%3EArno%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-848353%22%20slang%3D%22en-US%22%3ERe%3A%20In%20desperate%20need%20of%20a%20formula%20for%20a%20-basically-%20simple%20task%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-848353%22%20slang%3D%22en-US%22%3E%3CP%3EYou're%20welcome%20Arno!%3C%2FP%3E%3CP%3EThanks%20for%20the%20feedback.%3C%2FP%3E%3CP%3EPlease%20don't%20forget%20to%20mark%20the%20post%20with%20proposed%20solution%20as%20an%20Accepted%20Answer%20and%20hit%20the%20like%20button%20if%20you%20liked%20the%20solution%20provided.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Blogpapa
Occasional Contributor

I am building a test file in which to store test results but I am at a total loss. This is my problem: 

- Our products are sold in combination with 1, 2 or 3 accessories.
- Products are manufactured on demand, and from every batch a sample needs to be tested.
- We test the samples with only 1 of the possible accessories at a time: a matter of cost and time.
- We first test product 1 with accessory 1. The next time product 1 needs to be tested, we do it with accessory 2, the third time with accessory 3 and the fourth time with accessory 1 again. The same goes for all of our products.

 

Which formula will return the right accessory to test with next?

 

9 Replies

@Blogpapa 

Please try this and see if this is what you were trying to achieve...

 

In H3

=INDEX(Table1[[accessory 1]:[accessory 3]],MATCH([@product],Table1[product],0),COUNTIF(F$3:F3,F3))

 

@Subodh_Tiwari_sktneer This seems to be working I still have to translate it to the actual file but if this works, you are truly a life saver! Thank you so much!

@Subodh_Tiwari_sktneer It sounded too good to be true... It works perfectly for the first part, but two things are not working yet:

- after the 3 accessory options are tested, the fourth test should use accessory 1 again, the fifth test should use accessory 3 again, et cetera. So, a continuous loop.

- products that have only 2 test accessories should cycle through those 2 (1-2-1-2-1-2 instead of 1-2-0-1-2-0)

 

Think you can figure that out? That would be amazing

@Blogpapa 

If you tweak your master data to fill the empty cells for any product, if any, manually, the following Array Formula will return the desired output.

e.g. Cell D5 in your master data is empty which you can fill manually with the first occurrence of the accessory which is 'a' in this case. I have filled it manually in the master data.

See if you can tweak the master data manually for those empty cells if any. If yes, you can try the following Array Formula which requires confirmation with Ctrl+Shift+Enter instead Enter alone...

 

In H3

=IFERROR(INDEX(Table1[[accessory 1]:[accessory 3]],MATCH([@product],Table1[product],0),COUNTIF(F$3:F3,F3)),INDEX(H$2:H2,SMALL(IF(F$2:F2=[@product],ROW(F$2:F2)-ROW($F$2)+1),IF(MOD(COUNTIF(F$2:F2,[@product]),COUNTA(INDEX(Table1[[accessory 1]:[accessory 3]],MATCH([@product],Table1[product],0),)))=0,1,MOD(COUNTIF(F$2:F2,[@product]),COUNTA(INDEX(Table1[[accessory 1]:[accessory 3]],MATCH([@product],Table1[product],0),)))+1))))

Then confirm with Ctrl+Shift+Enter instead of Enter alone.

If you have access to the dynamic array formula, you don't need the special key stroke Ctrl+Shift+Enter to confirm an array formula, Enter is enough in that case.

 

 

@Subodh_Tiwari_sktneer 

 

We're almost there! The only thing is: with this formula product 3 would be tested with accessories a-d-a-a-d-a-a-d-a-a-d-a, whil it should be a-d-a-d-a-d-a-d. This way I would test twice as much with 'a' as with 'd'. That would corrupt the outcome... 

Highlighted

@Blogpapa 

Please find the attached and let me know if the formula is working as desired now.

I am going to post the formula in the next reply because last time when I posted the formula, somehow the site considered it as spam and didn't allow me to post it.

 

 

Solution

@Blogpapa 

Here is the tweaked formula...

 

=INDEX(Table1[[accessory 1]:[accessory 3]],MATCH([@product],Table1[product],0),IF(MOD(COUNTIF(F$3:F3,F3),COUNTA(INDEX(Table1[[accessory 1]:[accessory 3]],MATCH([@product],Table1[product],0),)))=0,COUNTA(INDEX(Table1[[accessory 1]:[accessory 3]],MATCH([@product],Table1[product],0),)),MOD(COUNTIF(F$3:F3,F3),COUNTA(INDEX(Table1[[accessory 1]:[accessory 3]],MATCH([@product],Table1[product],0),)))))

Holy crap @Subodh_Tiwari_sktneer !

 

That looks fantastic! I will build it in my masterfile and post the outcome here. This is really great stuff, thanks a lot!

 

Kind regards,

Arno 

You're welcome Arno!

Thanks for the feedback.

Please don't forget to mark the post with proposed solution as an Accepted Answer and hit the like button if you liked the solution provided.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 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
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies