Analyse Sales Data

[salongirl77]

Hi Guys.

We receive a daily CSV file from one of our biggest customers. It appears as follows:

Debtor name Product Code Product Description Quantity Invoiced
New World 1 201009 Crunchy Bar 2
New World 1 203009 Tooth brush 7
New World 1 207777 Drink container 3
Countdown 2 201009 Crunchy Bar 6
Countdown 2 209999 Lipstick 4

I need to create a formula that would read through all the data and return the following information:

how many crunchy bars did New World 1 order today, if they didnt order any, then the cell next to the debtors name would be marked in red. If they did order, then the value of '2' would appear next to the debtors name.

any help would be appreciated.

thank you soo much


<table style="border-collapse: collapse; width: 552pt;" border="0" cellpadding="0" cellspacing="0" width="735"><col style="width: 189pt;" width="252"> <col style="width: 68pt;" width="90"> <col style="width: 206pt;" width="275"> <col style="width: 89pt;" width="118"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 189pt;" height="20" width="252"> <table style="border-collapse: collapse; width: 345pt;" border="0" cellpadding="0" cellspacing="0" width="458"><col style="width: 84pt;" width="112"> <col style="width: 68pt;" width="90"> <col style="width: 104pt;" width="138"> <col style="width: 89pt;" width="118"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 84pt;" height="20" width="112">
</td> <td class="xl65" style="border-left: medium none; width: 68pt;" width="90">
</td> <td class="xl65" style="border-left: medium none; width: 104pt;" width="138">
</td> <td class="xl65" style="border-left: medium none; width: 89pt;" width="118">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">
</td> </tr> </tbody></table></td> <td style="width: 68pt;" width="90">
</td> <td style="width: 206pt;" width="275">
</td> <td style="width: 89pt;" width="118">Quantity Invoiced</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td align="right">
</td> <td>
</td> <td align="right">57</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td align="right">
</td> <td>
</td> <td align="right">15</td> </tr> </tbody></table>

 

[salongirl77]

Sorry, I forgot to mention that I have been told that the INDEX and MATCH function would be of help. However, I am unsure of how to write a formula that would read more than 2 values.

Thansk