| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sales Report
Dear All,
I have a database in sheet A. the fields such as Sales Name, Product, Unit Price, etc. (E.g. the database contains 100Records, where Sales Name are repetitive depends on the product sold). More spesific, there are 9 Sales Name in that databse. I would like to make the Sales based on Sales Name in Sheet B. So i need list down those 9 Sales Name using FORMULA (i dont want to use Pivot table). Please guide to to display those name in Sheet B, Cell A1:A9??? Thank you for your kind attention and help. Respectfully, andri |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sales Report
'Sheet B'!A2: ='Sheet A'!A2 'Sheet B'!A3: =IF(ISERROR(MATCH(0,COUNTIF(A$2:A2,'Sheet A'!$A$2:$A$20&""),0)),"", INDEX(IF(ISBLANK('Sheet A'!$A$2:$A$20),"",'Sheet A'!$A$2:$A$20),MATCH(0,COUNTIF(A$2:A2,'Sheet A'!$A$2:$A$20&""),0))) this is an array formula, so commit with Ctrl-Shift-Enter, not just Enter, and copy down -- __________________________________ HTH Bob "Andri" wrote in message ... Dear All, I have a database in sheet A. the fields such as Sales Name, Product, Unit Price, etc. (E.g. the database contains 100Records, where Sales Name are repetitive depends on the product sold). More spesific, there are 9 Sales Name in that databse. I would like to make the Sales based on Sales Name in Sheet B. So i need list down those 9 Sales Name using FORMULA (i dont want to use Pivot table). Please guide to to display those name in Sheet B, Cell A1:A9??? Thank you for your kind attention and help. Respectfully, andri |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sales Report
Dear Bob,
The result is 0. I have use the Evaluate Formula, the final condition before 0 is "=IF(FALSE,#N/A,{"Andri"}. Which is the result should be "Andri" instead of 0. why? Please guide further if i revise the scenario as follows: A1: A6, contains Sales Name as follows: "Andri", "Jeffrey","Jeffrey","Katarina","Wina","Wina". How to use the previous formula, so the result as follows B1:B4 "Andri" "Jeffrey" "Katarina" "Wina" Still think your formula :) Thank you for your kind help in this matter. Respectfully, Andri "Bob Phillips" wrote: 'Sheet B'!A2: ='Sheet A'!A2 'Sheet B'!A3: =IF(ISERROR(MATCH(0,COUNTIF(A$2:A2,'Sheet A'!$A$2:$A$20&""),0)),"", INDEX(IF(ISBLANK('Sheet A'!$A$2:$A$20),"",'Sheet A'!$A$2:$A$20),MATCH(0,COUNTIF(A$2:A2,'Sheet A'!$A$2:$A$20&""),0))) this is an array formula, so commit with Ctrl-Shift-Enter, not just Enter, and copy down -- __________________________________ HTH Bob "Andri" wrote in message ... Dear All, I have a database in sheet A. the fields such as Sales Name, Product, Unit Price, etc. (E.g. the database contains 100Records, where Sales Name are repetitive depends on the product sold). More spesific, there are 9 Sales Name in that databse. I would like to make the Sales based on Sales Name in Sheet B. So i need list down those 9 Sales Name using FORMULA (i dont want to use Pivot table). Please guide to to display those name in Sheet B, Cell A1:A9??? Thank you for your kind attention and help. Respectfully, andri |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sales Report
Dear Bob,
I got it....thank you for the help. I miss the first Criteria "'Sheet B'!A2: ='Sheet A'!A2" Thank you for your excellent formula... i amazed of it. Respectfully, andri "Bob Phillips" wrote: 'Sheet B'!A2: ='Sheet A'!A2 'Sheet B'!A3: =IF(ISERROR(MATCH(0,COUNTIF(A$2:A2,'Sheet A'!$A$2:$A$20&""),0)),"", INDEX(IF(ISBLANK('Sheet A'!$A$2:$A$20),"",'Sheet A'!$A$2:$A$20),MATCH(0,COUNTIF(A$2:A2,'Sheet A'!$A$2:$A$20&""),0))) this is an array formula, so commit with Ctrl-Shift-Enter, not just Enter, and copy down -- __________________________________ HTH Bob "Andri" wrote in message ... Dear All, I have a database in sheet A. the fields such as Sales Name, Product, Unit Price, etc. (E.g. the database contains 100Records, where Sales Name are repetitive depends on the product sold). More spesific, there are 9 Sales Name in that databse. I would like to make the Sales based on Sales Name in Sheet B. So i need list down those 9 Sales Name using FORMULA (i dont want to use Pivot table). Please guide to to display those name in Sheet B, Cell A1:A9??? Thank you for your kind attention and help. Respectfully, andri |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sales Report
Andri,
You can also use DataFilterAdvanced Filter which has an option to filter unique values only. -- __________________________________ HTH Bob "Andri" wrote in message ... Dear Bob, I got it....thank you for the help. I miss the first Criteria "'Sheet B'!A2: ='Sheet A'!A2" Thank you for your excellent formula... i amazed of it. Respectfully, andri "Bob Phillips" wrote: 'Sheet B'!A2: ='Sheet A'!A2 'Sheet B'!A3: =IF(ISERROR(MATCH(0,COUNTIF(A$2:A2,'Sheet A'!$A$2:$A$20&""),0)),"", INDEX(IF(ISBLANK('Sheet A'!$A$2:$A$20),"",'Sheet A'!$A$2:$A$20),MATCH(0,COUNTIF(A$2:A2,'Sheet A'!$A$2:$A$20&""),0))) this is an array formula, so commit with Ctrl-Shift-Enter, not just Enter, and copy down -- __________________________________ HTH Bob "Andri" wrote in message ... Dear All, I have a database in sheet A. the fields such as Sales Name, Product, Unit Price, etc. (E.g. the database contains 100Records, where Sales Name are repetitive depends on the product sold). More spesific, there are 9 Sales Name in that databse. I would like to make the Sales based on Sales Name in Sheet B. So i need list down those 9 Sales Name using FORMULA (i dont want to use Pivot table). Please guide to to display those name in Sheet B, Cell A1:A9??? Thank you for your kind attention and help. Respectfully, andri |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sales Report
Dear Bob,
Yes, but i need the Formula solution in preparing the report. Thank you. Respectfully, Andri "Bob Phillips" wrote: Andri, You can also use DataFilterAdvanced Filter which has an option to filter unique values only. -- __________________________________ HTH Bob "Andri" wrote in message ... Dear Bob, I got it....thank you for the help. I miss the first Criteria "'Sheet B'!A2: ='Sheet A'!A2" Thank you for your excellent formula... i amazed of it. Respectfully, andri "Bob Phillips" wrote: 'Sheet B'!A2: ='Sheet A'!A2 'Sheet B'!A3: =IF(ISERROR(MATCH(0,COUNTIF(A$2:A2,'Sheet A'!$A$2:$A$20&""),0)),"", INDEX(IF(ISBLANK('Sheet A'!$A$2:$A$20),"",'Sheet A'!$A$2:$A$20),MATCH(0,COUNTIF(A$2:A2,'Sheet A'!$A$2:$A$20&""),0))) this is an array formula, so commit with Ctrl-Shift-Enter, not just Enter, and copy down -- __________________________________ HTH Bob "Andri" wrote in message ... Dear All, I have a database in sheet A. the fields such as Sales Name, Product, Unit Price, etc. (E.g. the database contains 100Records, where Sales Name are repetitive depends on the product sold). More spesific, there are 9 Sales Name in that databse. I would like to make the Sales based on Sales Name in Sheet B. So i need list down those 9 Sales Name using FORMULA (i dont want to use Pivot table). Please guide to to display those name in Sheet B, Cell A1:A9??? Thank you for your kind attention and help. Respectfully, andri |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sales Report
I thought that might be the case, but I just thought to mention it.
-- __________________________________ HTH Bob "Andri" wrote in message ... Dear Bob, Yes, but i need the Formula solution in preparing the report. Thank you. Respectfully, Andri "Bob Phillips" wrote: Andri, You can also use DataFilterAdvanced Filter which has an option to filter unique values only. -- __________________________________ HTH Bob "Andri" wrote in message ... Dear Bob, I got it....thank you for the help. I miss the first Criteria "'Sheet B'!A2: ='Sheet A'!A2" Thank you for your excellent formula... i amazed of it. Respectfully, andri "Bob Phillips" wrote: 'Sheet B'!A2: ='Sheet A'!A2 'Sheet B'!A3: =IF(ISERROR(MATCH(0,COUNTIF(A$2:A2,'Sheet A'!$A$2:$A$20&""),0)),"", INDEX(IF(ISBLANK('Sheet A'!$A$2:$A$20),"",'Sheet A'!$A$2:$A$20),MATCH(0,COUNTIF(A$2:A2,'Sheet A'!$A$2:$A$20&""),0))) this is an array formula, so commit with Ctrl-Shift-Enter, not just Enter, and copy down -- __________________________________ HTH Bob "Andri" wrote in message ... Dear All, I have a database in sheet A. the fields such as Sales Name, Product, Unit Price, etc. (E.g. the database contains 100Records, where Sales Name are repetitive depends on the product sold). More spesific, there are 9 Sales Name in that databse. I would like to make the Sales based on Sales Name in Sheet B. So i need list down those 9 Sales Name using FORMULA (i dont want to use Pivot table). Please guide to to display those name in Sheet B, Cell A1:A9??? Thank you for your kind attention and help. Respectfully, andri |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sales Report
Dear Bob,
thank you for your concern and help. respectfully, andri "Bob Phillips" wrote: I thought that might be the case, but I just thought to mention it. -- __________________________________ HTH Bob "Andri" wrote in message ... Dear Bob, Yes, but i need the Formula solution in preparing the report. Thank you. Respectfully, Andri "Bob Phillips" wrote: Andri, You can also use DataFilterAdvanced Filter which has an option to filter unique values only. -- __________________________________ HTH Bob "Andri" wrote in message ... Dear Bob, I got it....thank you for the help. I miss the first Criteria "'Sheet B'!A2: ='Sheet A'!A2" Thank you for your excellent formula... i amazed of it. Respectfully, andri "Bob Phillips" wrote: 'Sheet B'!A2: ='Sheet A'!A2 'Sheet B'!A3: =IF(ISERROR(MATCH(0,COUNTIF(A$2:A2,'Sheet A'!$A$2:$A$20&""),0)),"", INDEX(IF(ISBLANK('Sheet A'!$A$2:$A$20),"",'Sheet A'!$A$2:$A$20),MATCH(0,COUNTIF(A$2:A2,'Sheet A'!$A$2:$A$20&""),0))) this is an array formula, so commit with Ctrl-Shift-Enter, not just Enter, and copy down -- __________________________________ HTH Bob "Andri" wrote in message ... Dear All, I have a database in sheet A. the fields such as Sales Name, Product, Unit Price, etc. (E.g. the database contains 100Records, where Sales Name are repetitive depends on the product sold). More spesific, there are 9 Sales Name in that databse. I would like to make the Sales based on Sales Name in Sheet B. So i need list down those 9 Sales Name using FORMULA (i dont want to use Pivot table). Please guide to to display those name in Sheet B, Cell A1:A9??? Thank you for your kind attention and help. Respectfully, andri |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sales tracking report | Excel Worksheet Functions | |||
| Add Sales Goals to Sales Report in Pivot Table | Excel Discussion (Misc queries) | |||
| Sales Report Grrrr | Excel Worksheet Functions | |||
| Formula help in a monthly sales report. | Excel Worksheet Functions | |||
Linear Mode
