I am using the following formula to look down column B for Left two characters and then down column D or text in D11 and then down column G.
This gives me a result something like this:
Office 1 AAA, BBB, CCC
Office 2 BBB, CCC
Office 3 BBB
I now need the formula to list just one instance:
Office 1 AAA
Office 1 BBB
I have been trying to modify the formula but can't get it to work.
Also, if anyone has time--what does MCONCAT and UNIQUEVALUES do?? I can not find them in the Excel Help file. Just came in the office to ge Walkenbach "Excel 2000 Formulas" so I can see if I can find it in there...
SUBSTITUTE(MCONCAT(UNIQUEVALUES(IF((LEFT('afw-2 Test-Bags'!$B$11:$B$110,2)=D33)*('afw-2 Test-Bags'!$E$11:$E$110=D11),", "&'afw-2 Test-Bags'!$G$11:$G$110,""))),", ","",1)
This gives me a result something like this:
Office 1 AAA, BBB, CCC
Office 2 BBB, CCC
Office 3 BBB
I now need the formula to list just one instance:
Office 1 AAA
Office 1 BBB
I have been trying to modify the formula but can't get it to work.
Also, if anyone has time--what does MCONCAT and UNIQUEVALUES do?? I can not find them in the Excel Help file. Just came in the office to ge Walkenbach "Excel 2000 Formulas" so I can see if I can find it in there...
SUBSTITUTE(MCONCAT(UNIQUEVALUES(IF((LEFT('afw-2 Test-Bags'!$B$11:$B$110,2)=D33)*('afw-2 Test-Bags'!$E$11:$E$110=D11),", "&'afw-2 Test-Bags'!$G$11:$G$110,""))),", ","",1)