Hi guys,
I took a bit to understand how the lookup function works in Excel, but after you know some tricks it is very useful (you can even use it in your little monster of 150K).
Ok, first of all, you need to understand that the values to show in your "main table" need to be on the left of your sheet. I tried on a different sheet and it did not work.
So the issue I faced was when I tried to identify in Excel if a Business Partner was, at the same time, Customer and Supplier.
So as you can see in the picture for Business Partner 20 we have 2 entries that say the business partner is customer and 2 entries that says that the business partner is a supplier.
To fulfill the request I started by creating the Business partner ID list in the column A (Home --> fill --> series
after that, I created two extra columns (B and C), B is for mark if the business partner was identified as a customer and C other if the business partner was identified as a supplier in the original data.
So doing an and between B and C I got the flag of the Business Partner as Customer/Partner (column D)
Formulas used
B: =COUNTIFS('All BPs'!E:E;'All BPs'!A2;'All BPs'!P:P; "*Cliente*")
C: =COUNTIFS('All BPs'!E:E;'All BPs'!A2;'All BPs'!P:P; "*Proveedor*")
D: = AND(B2;C2)
Lookup: =LOOKUP(E7;A6:A150005;X6:X150005) --> not sure that this is correct
I hope this is helpful.
Soon or later you will have to use excel for some crazy requirement stuff. better know as much as possible.
