Count partial matched text string

C

Chapi

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 G’s 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
 
L

Luke M

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,'Supplies
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.
 
C

Chapi

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
 
C

Chapi

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))))))
 
L

Luke M

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.
 
Top