| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Count partial matched text string
I have 3 spreadsheets. The first, Supplies Requests Received where Column
B contains the (duplicated) names of internal offices placing supply requests (e.g., SNF, DGEN, CANM, etc.) and Column G contains each date a request for supplies was received from that office. Column B Column B SNF 1//29/2009 DGEN 4/19/2008 CNMN 2/4/2009 ASEV 12/11/2008 SNF 2/3/2009 ASEV 9/16/2008 DGEN 5/1/2008 The second spreadsheet Supplies Delivered where Column A is a link of Column B from the above Supplies Requests Received spreadsheet and Column G (of Supplies Delivered) contains the names of ALL items delivered to that office based on each supply request received. Column A Column G SNF 20 lb. white paper; #2 pencil; 11:1.1 black ink pen DGEN 20 lb. white paper; 2:.2.12 toner cartridge; 11:9.9 red ink pen CNMN #2 pencil, 31:01.9 desk calendar; 3:14.7 desk lamp ASEV 25 lb. goldenrod paper; #2 pencils; 11:1.1 black ink pen SNF 77:3.1 steno pad ABEV 83:6.2 paper clips; 4:1.8 tape dispenser, 84:.8.3 desk stapler DGEN 20 lb. white paper The third spreadsheet, 2009 Master Supply List where Column A (A2:A200) contains unduplicated supply item names, and the headings for Column B thru Column E is the name of one of internal offices (i.e., SNF, DGEN, CNMN, ASEV, etc.) (Col B) (Col C) (Col D) (Col E) Column A SNF DGEM CNMN ASEV 20 lb. white paper #2 pencil 11:1.1 black ink pen 25 lb. goldenrod paper 77:3.1 steno pad 83:6.2 paper clips 4:1.8 tape dispenser 84:.8.3 desk stapler I want the 2009 Master Supply List to count the number or times each office is delivered any item listed on the 2009 Master Supply List during a specific year (e.g., 2009). The problem, (a text string) multiple items are listed in Column Gs cells of the Supplies Delivered spreadsheet; I cannot figure a way to count a match from the 2009 Master Supply List with that of the same item delivered to a specific office during a specific year. Any suggestions would be much appreciated |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Count partial matched text string
This assumes the dates you have on first sheet are actually entered as dates
In B2 =SUMPRODUCT(('Supplies Requests Received'!$A$1:$A$100=B$1)*(YEAR('Supplies Requests Received'!$G$1:$G$100)=2009)*(ISNUMBER(FIND($A2,'S upplies Delivered'!$G$1:$G$100)))) Adjust array sizes as needed. You should then be able to copy this cell down and to the right to get totals for rest of your data. Note that you could change the '2009' to a cell reference somewhere, to make formula more dynamic. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Chapi" wrote: I have 3 spreadsheets. The first, Supplies Requests Received where Column B contains the (duplicated) names of internal offices placing supply requests (e.g., SNF, DGEN, CANM, etc.) and Column G contains each date a request for supplies was received from that office. Column B Column B SNF 1//29/2009 DGEN 4/19/2008 CNMN 2/4/2009 ASEV 12/11/2008 SNF 2/3/2009 ASEV 9/16/2008 DGEN 5/1/2008 The second spreadsheet Supplies Delivered where Column A is a link of Column B from the above Supplies Requests Received spreadsheet and Column G (of Supplies Delivered) contains the names of ALL items delivered to that office based on each supply request received. Column A Column G SNF 20 lb. white paper; #2 pencil; 11:1.1 black ink pen DGEN 20 lb. white paper; 2:.2.12 toner cartridge; 11:9.9 red ink pen CNMN #2 pencil, 31:01.9 desk calendar; 3:14.7 desk lamp ASEV 25 lb. goldenrod paper; #2 pencils; 11:1.1 black ink pen SNF 77:3.1 steno pad ABEV 83:6.2 paper clips; 4:1.8 tape dispenser, 84:.8.3 desk stapler DGEN 20 lb. white paper The third spreadsheet, 2009 Master Supply List where Column A (A2:A200) contains unduplicated supply item names, and the headings for Column B thru Column E is the name of one of internal offices (i.e., SNF, DGEN, CNMN, ASEV, etc.) (Col B) (Col C) (Col D) (Col E) Column A SNF DGEM CNMN ASEV 20 lb. white paper #2 pencil 11:1.1 black ink pen 25 lb. goldenrod paper 77:3.1 steno pad 83:6.2 paper clips 4:1.8 tape dispenser 84:.8.3 desk stapler I want the 2009 Master Supply List to count the number or times each office is delivered any item listed on the 2009 Master Supply List during a specific year (e.g., 2009). The problem, (a text string) multiple items are listed in Column Gs cells of the Supplies Delivered spreadsheet; I cannot figure a way to count a match from the 2009 Master Supply List with that of the same item delivered to a specific office during a specific year. Any suggestions would be much appreciated |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Count partial matched text string
Unfortunately cannot get it to work, it only shows zeros. It should show the
totals I entered in 2009 Master Supply List below. (I also corrected Supplies Request Received Column reference.) Supplies Requests Received Column B Column G SNF 1//29/2009 DGEN 4/19/2008 CNMN 2/4/2009 ASEV 12/11/2008 SNF 2/3/2009 ASEV 9/16/2008 DGEN 5/1/2008 Supplies Delivered Column A Column G SNF 20 lb. white paper; #2 pencil; 11:1.1 black ink pen DGEN 20 lb. white paper; 2:.2.12 toner cartridge; 11:9.9 red ink pen CNMN #2 pencil, 31:01.9 desk calendar; 3:14.7 desk lamp ASEV 25 lb. goldenrod paper; #2 pencils; 11:1.1 black ink pen SNF 77:3.1 steno pad ASEV 83:6.2 paper clips; 4:1.8 tape dispenser, 84:.8.3 desk stapler DGEN 20 lb. white paper 2009 Master Supply List Column A (Col B) (Col C) (Col D) (Col E) SNF DGEN CNMN ASEV 20 lb. white paper 1 2 0 0 #2 pencil 1 0 1 1 11:1.1 black ink pen 1 0 0 1 25 lb. goldenrod paper 0 0 0 1 77:3.1 steno pad 1 0 0 0 "Luke M" wrote: This assumes the dates you have on first sheet are actually entered as dates In B2 =SUMPRODUCT(('Supplies Requests Received'!$A$1:$A$100=B$1)*(YEAR('Supplies Requests Received'!$G$1:$G$100)=2009)*(ISNUMBER(FIND($A2,'S upplies Delivered'!$G$1:$G$100)))) Adjust array sizes as needed. You should then be able to copy this cell down and to the right to get totals for rest of your data. Note that you could change the '2009' to a cell reference somewhere, to make formula more dynamic. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Chapi" wrote: I have 3 spreadsheets. The first, Supplies Requests Received where Column B contains the (duplicated) names of internal offices placing supply requests (e.g., SNF, DGEN, CANM, etc.) and Column G contains each date a request for supplies was received from that office. Column B Column B SNF 1//29/2009 DGEN 4/19/2008 CNMN 2/4/2009 ASEV 12/11/2008 SNF 2/3/2009 ASEV 9/16/2008 DGEN 5/1/2008 The second spreadsheet Supplies Delivered where Column A is a link of Column B from the above Supplies Requests Received spreadsheet and Column G (of Supplies Delivered) contains the names of ALL items delivered to that office based on each supply request received. Column A Column G SNF 20 lb. white paper; #2 pencil; 11:1.1 black ink pen DGEN 20 lb. white paper; 2:.2.12 toner cartridge; 11:9.9 red ink pen CNMN #2 pencil, 31:01.9 desk calendar; 3:14.7 desk lamp ASEV 25 lb. goldenrod paper; #2 pencils; 11:1.1 black ink pen SNF 77:3.1 steno pad ABEV 83:6.2 paper clips; 4:1.8 tape dispenser, 84:.8.3 desk stapler DGEN 20 lb. white paper The third spreadsheet, 2009 Master Supply List where Column A (A2:A200) contains unduplicated supply item names, and the headings for Column B thru Column E is the name of one of internal offices (i.e., SNF, DGEN, CNMN, ASEV, etc.) (Col B) (Col C) (Col D) (Col E) Column A SNF DGEM CNMN ASEV 20 lb. white paper #2 pencil 11:1.1 black ink pen 25 lb. goldenrod paper 77:3.1 steno pad 83:6.2 paper clips 4:1.8 tape dispenser 84:.8.3 desk stapler I want the 2009 Master Supply List to count the number or times each office is delivered any item listed on the 2009 Master Supply List during a specific year (e.g., 2009). The problem, (a text string) multiple items are listed in Column Gs cells of the Supplies Delivered spreadsheet; I cannot figure a way to count a match from the 2009 Master Supply List with that of the same item delivered to a specific office during a specific year. Any suggestions would be much appreciated |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Count partial matched text string
I currently have to enter the formula below in individual cells of the 2009
Master List under each column named for an office (e.g., SNF) and correspond to the same row with the same name of the supply item (e.g., 11:1.1. black ink pen). I have 2000 rows and 10 columns, which makes this process time consuming because I have to keep change the name of the supply item to match the appropriate row and office name to match the appropriate column. Anyway to automatically count (by matching) the supply item name on the Supplies Delivered spreadsheet with the same supply item named on the Supplies Delivered spreadsheet and by office and by year? =SUMPRODUCT(--(TEXT(Supplies Requests Received!$G2:$G2000,"yyyy")="2009"),--(Supplies Requests Received!$B2:$B2000="SNF"),--(--(NOT(ISERROR(SEARCH("11:1.1 black ink pen",Supplies Delivered!$G2:$G2000)))))) "Chapi" wrote: Unfortunately cannot get it to work, it only shows zeros. It should show the totals I entered in 2009 Master Supply List below. (I also corrected Supplies Request Received Column reference.) Supplies Requests Received Column B Column G SNF 1//29/2009 DGEN 4/19/2008 CNMN 2/4/2009 ASEV 12/11/2008 SNF 2/3/2009 ASEV 9/16/2008 DGEN 5/1/2008 Supplies Delivered Column A Column G SNF 20 lb. white paper; #2 pencil; 11:1.1 black ink pen DGEN 20 lb. white paper; 2:.2.12 toner cartridge; 11:9.9 red ink pen CNMN #2 pencil, 31:01.9 desk calendar; 3:14.7 desk lamp ASEV 25 lb. goldenrod paper; #2 pencils; 11:1.1 black ink pen SNF 77:3.1 steno pad ASEV 83:6.2 paper clips; 4:1.8 tape dispenser, 84:.8.3 desk stapler DGEN 20 lb. white paper 2009 Master Supply List Column A (Col B) (Col C) (Col D) (Col E) SNF DGEN CNMN ASEV 20 lb. white paper 1 2 0 0 #2 pencil 1 0 1 1 11:1.1 black ink pen 1 0 0 1 25 lb. goldenrod paper 0 0 0 1 77:3.1 steno pad 1 0 0 0 "Luke M" wrote: This assumes the dates you have on first sheet are actually entered as dates In B2 =SUMPRODUCT(('Supplies Requests Received'!$A$1:$A$100=B$1)*(YEAR('Supplies Requests Received'!$G$1:$G$100)=2009)*(ISNUMBER(FIND($A2,'S upplies Delivered'!$G$1:$G$100)))) Adjust array sizes as needed. You should then be able to copy this cell down and to the right to get totals for rest of your data. Note that you could change the '2009' to a cell reference somewhere, to make formula more dynamic. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Chapi" wrote: I have 3 spreadsheets. The first, Supplies Requests Received where Column B contains the (duplicated) names of internal offices placing supply requests (e.g., SNF, DGEN, CANM, etc.) and Column G contains each date a request for supplies was received from that office. Column B Column B SNF 1//29/2009 DGEN 4/19/2008 CNMN 2/4/2009 ASEV 12/11/2008 SNF 2/3/2009 ASEV 9/16/2008 DGEN 5/1/2008 The second spreadsheet Supplies Delivered where Column A is a link of Column B from the above Supplies Requests Received spreadsheet and Column G (of Supplies Delivered) contains the names of ALL items delivered to that office based on each supply request received. Column A Column G SNF 20 lb. white paper; #2 pencil; 11:1.1 black ink pen DGEN 20 lb. white paper; 2:.2.12 toner cartridge; 11:9.9 red ink pen CNMN #2 pencil, 31:01.9 desk calendar; 3:14.7 desk lamp ASEV 25 lb. goldenrod paper; #2 pencils; 11:1.1 black ink pen SNF 77:3.1 steno pad ABEV 83:6.2 paper clips; 4:1.8 tape dispenser, 84:.8.3 desk stapler DGEN 20 lb. white paper The third spreadsheet, 2009 Master Supply List where Column A (A2:A200) contains unduplicated supply item names, and the headings for Column B thru Column E is the name of one of internal offices (i.e., SNF, DGEN, CNMN, ASEV, etc.) (Col B) (Col C) (Col D) (Col E) Column A SNF DGEM CNMN ASEV 20 lb. white paper #2 pencil 11:1.1 black ink pen 25 lb. goldenrod paper 77:3.1 steno pad 83:6.2 paper clips 4:1.8 tape dispenser 84:.8.3 desk stapler I want the 2009 Master Supply List to count the number or times each office is delivered any item listed on the 2009 Master Supply List during a specific year (e.g., 2009). The problem, (a text string) multiple items are listed in Column Gs cells of the Supplies Delivered spreadsheet; I cannot figure a way to count a match from the 2009 Master Supply List with that of the same item delivered to a specific office during a specific year. Any suggestions would be much appreciated |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Count partial matched text string
You should be able to just replace your text with cell references.
=SUMPRODUCT(--(TEXT(Supplies Requests Received!$G$2:$G$2000,"yyyy")="2009"),--(Supplies Requests Received!$B$2:$B$2000=B$1),--(--(NOT(ISERROR(SEARCH($A2,Supplies Delivered!$G$2:$G$2000)))))) Since you didn't say if you had the year listed anyway, I can't reference a cell for that one. If it's in A1, change the "2009" to $A$1 putting this into cell B2 of your table should give you total for SNF 20lb white paper. The references are set so that you can copy the cell to the other spaces you need. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Chapi" wrote: I currently have to enter the formula below in individual cells of the 2009 Master List under each column named for an office (e.g., SNF) and correspond to the same row with the same name of the supply item (e.g., 11:1.1. black ink pen). I have 2000 rows and 10 columns, which makes this process time consuming because I have to keep change the name of the supply item to match the appropriate row and office name to match the appropriate column. Anyway to automatically count (by matching) the supply item name on the Supplies Delivered spreadsheet with the same supply item named on the Supplies Delivered spreadsheet and by office and by year? =SUMPRODUCT(--(TEXT(Supplies Requests Received!$G2:$G2000,"yyyy")="2009"),--(Supplies Requests Received!$B2:$B2000="SNF"),--(--(NOT(ISERROR(SEARCH("11:1.1 black ink pen",Supplies Delivered!$G2:$G2000)))))) "Chapi" wrote: Unfortunately cannot get it to work, it only shows zeros. It should show the totals I entered in 2009 Master Supply List below. (I also corrected Supplies Request Received Column reference.) Supplies Requests Received Column B Column G SNF 1//29/2009 DGEN 4/19/2008 CNMN 2/4/2009 ASEV 12/11/2008 SNF 2/3/2009 ASEV 9/16/2008 DGEN 5/1/2008 Supplies Delivered Column A Column G SNF 20 lb. white paper; #2 pencil; 11:1.1 black ink pen DGEN 20 lb. white paper; 2:.2.12 toner cartridge; 11:9.9 red ink pen CNMN #2 pencil, 31:01.9 desk calendar; 3:14.7 desk lamp ASEV 25 lb. goldenrod paper; #2 pencils; 11:1.1 black ink pen SNF 77:3.1 steno pad ASEV 83:6.2 paper clips; 4:1.8 tape dispenser, 84:.8.3 desk stapler DGEN 20 lb. white paper 2009 Master Supply List Column A (Col B) (Col C) (Col D) (Col E) SNF DGEN CNMN ASEV 20 lb. white paper 1 2 0 0 #2 pencil 1 0 1 1 11:1.1 black ink pen 1 0 0 1 25 lb. goldenrod paper 0 0 0 1 77:3.1 steno pad 1 0 0 0 "Luke M" wrote: This assumes the dates you have on first sheet are actually entered as dates In B2 =SUMPRODUCT(('Supplies Requests Received'!$A$1:$A$100=B$1)*(YEAR('Supplies Requests Received'!$G$1:$G$100)=2009)*(ISNUMBER(FIND($A2,'S upplies Delivered'!$G$1:$G$100)))) Adjust array sizes as needed. You should then be able to copy this cell down and to the right to get totals for rest of your data. Note that you could change the '2009' to a cell reference somewhere, to make formula more dynamic. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Chapi" wrote: I have 3 spreadsheets. The first, Supplies Requests Received where Column B contains the (duplicated) names of internal offices placing supply requests (e.g., SNF, DGEN, CANM, etc.) and Column G contains each date a request for supplies was received from that office. Column B Column B SNF 1//29/2009 DGEN 4/19/2008 CNMN 2/4/2009 ASEV 12/11/2008 SNF 2/3/2009 ASEV 9/16/2008 DGEN 5/1/2008 The second spreadsheet Supplies Delivered where Column A is a link of Column B from the above Supplies Requests Received spreadsheet and Column G (of Supplies Delivered) contains the names of ALL items delivered to that office based on each supply request received. Column A Column G SNF 20 lb. white paper; #2 pencil; 11:1.1 black ink pen DGEN 20 lb. white paper; 2:.2.12 toner cartridge; 11:9.9 red ink pen CNMN #2 pencil, 31:01.9 desk calendar; 3:14.7 desk lamp ASEV 25 lb. goldenrod paper; #2 pencils; 11:1.1 black ink pen SNF 77:3.1 steno pad ABEV 83:6.2 paper clips; 4:1.8 tape dispenser, 84:.8.3 desk stapler DGEN 20 lb. white paper The third spreadsheet, 2009 Master Supply List where Column A (A2:A200) contains unduplicated supply item names, and the headings for Column B thru Column E is the name of one of internal offices (i.e., SNF, DGEN, CNMN, ASEV, etc.) (Col B) (Col C) (Col D) (Col E) Column A SNF DGEM CNMN ASEV 20 lb. white paper #2 pencil 11:1.1 black ink pen 25 lb. goldenrod paper 77:3.1 steno pad 83:6.2 paper clips 4:1.8 tape dispenser 84:.8.3 desk stapler I want the 2009 Master Supply List to count the number or times each office is delivered any item listed on the 2009 Master Supply List during a specific year (e.g., 2009). The problem, (a text string) multiple items are listed in Column Gs cells of the Supplies Delivered spreadsheet; I cannot figure a way to count a match from the 2009 Master Supply List with that of the same item delivered to a specific office during a specific year. Any suggestions would be much appreciated |
|
#6
|
|||
|
|||
|
According to the sheet I think you are in the business of pen and pencil. Thats really great thing and I appreciate that so much because one of the best business is pencil and pen business.
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Most frequent string in a column matched against a value in its ro | Excel Discussion (Misc queries) | |||
| return partial string | Excel Worksheet Functions | |||
| auto bold partial text in a string | Excel Discussion (Misc queries) | |||
| sumproduct partial text count | Excel Worksheet Functions | |||
| Partial String | Excel Discussion (Misc queries) | |||
Linear Mode
