recherche into a special range

[Maileen]

I have 15000 records into my Sheet1.
in column A, i have all publisher name.
in column B, i have all software name.

i have a sheet2 on which are :
column C : publisher name
column D : software name

I want to place in column E, the amount of couple Publisher+software name
for the same software name.

something like

Microsoft Excel2000
Microsoft Excel2000
Microsoft Excel2000
Microsoft Word2000
Microsoft Outlook2000
Microsoft Outlook2000

in E column, i should get result
3 for Microsoft Excel2000
1 for Microsoft Word2000
2 for Microsoft Outlook2000

I would like to do it like that:

1. based on sheet2 publisher name, sheet 1 (column A) refine a region/filter
2. based on sheet2 software name, sheet 1 (column B) refine a region/filter
3. count the number of record found and write it to sheet 2 in column E.

thanks for help,

Maileen

 

[Bob Phillips]

Hi Maileen,

Recherched

=SUMPRODUCT(--(Sheet1!$A$1:$A$15000=C1),--(Sheet1!$B$1:$B$15000=D1))&" for
"&C1&" "&D1

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Max]

One way to try ..

In Sheet1:
you have the data below in cols A and B,
data from row2 down

PubN SoftN
Microsoft Excel2000
Microsoft Excel2000
Microsoft Excel2000
Microsoft Word2000
Microsoft Outlook2000
Microsoft Outlook2000
etc

Use 2 helper cols to the right of the data in cols A and B (say col C and D)

Put in C2: =A2&" "&B2
Put in D2: =IF(C2="","",IF(COUNTIF($C$2:C2,C2)=1,ROW(),""))

Select C22, copy down by as many rows as data is expected in cols A and B,
say down to D1000. (Can copy down ahead of data input)

In Sheet2:

Put in say, C1: PubName SoftName (Just a label)

Put in C2:

=IF(ISERROR(MATCH(SMALL(Sheet1!D,ROWS($A$1:A1)),Sheet1!D,0)),"",INDEX(Sheet1!C:C,MATCH(SMALL(Sheet1!D,ROWS($A$1:A1)),Sheet1!D,0)))

Put in D2:
=IF(C2="","",COUNTIF(Sheet1!C:C,C2))

Select C22, copy down by as many rows as was done for cols C and D in
Sheet1, i.e. down to D1000

Col C will return the unique list of items from col C in Sheet1, with col D
returning the corresponding counts of the unique items listed (from col C in
Sheet1). For the sample data in Sheet1, you'll get:

PubName SoftName
Microsoft Excel2000____3
Microsoft Word2000____1
Microsoft Outlook2000__2
etc

 

[Max]

Oops, sorry, missed your line:
Just copy down the formulas in cols C and D in both sheets to say, D20000,

 

[Maileen]

Sorry,
but i don't understand your formula, could you explain it to me a little bit ?
thx,
Maileen

 

[Bob Phillips]

Basically, it counts the number of times the value in C1 occurs in
A1:A15000, AND, when B1 occurs in B1:B15000, and concatenates that with the
text to give the answer.

If you want a fuller explanation, take a look at
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tushar Mehta]

Create a PivotTable (Data | PivotTable and Chart...)* and filter as
desired. Use the Publisher and Software Names as row fields and the
software name as the data field. To filter, click the drop down
heading cells above the publisher and software names.

(*) The name of the menu item has changed slightly with different
versions of XL

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions