Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Maileen
 
Posts: n/a
Default recherche into a special range

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
  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default

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)


"Maileen" wrote in message
...
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



  #3   Report Post  
Max
 
Posts: n/a
Default

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 C2:D2, 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:D,ROWS($A$1:A1)), Sheet1!D:D,0)),"",INDEX(Sheet1!C:C,MATCH(SMALL(She et1!D:D,ROWS($A$1:A1)),Sheet1!D:D,0)))

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

Select C2:D2, 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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"Maileen" wrote:

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

  #4   Report Post  
Max
 
Posts: n/a
Default

Oops, sorry, missed your line:
I have 15000 records into my Sheet1.


Just copy down the formulas in cols C and D in both sheets to say, D20000,
instead of D1000 <g

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

  #5   Report Post  
Maileen
 
Posts: n/a
Default

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

"Bob Phillips" wrote:

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)


"Maileen" wrote in message
...
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






  #6   Report Post  
Bob Phillips
 
Posts: n/a
Default

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)


"Maileen" wrote in message
...
Sorry,
but i don't understand your formula, could you explain it to me a little

bit ?
thx,
Maileen

"Bob Phillips" wrote:

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)


"Maileen" wrote in message
...
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






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
ClearContents method on a passed range bryan New Users to Excel 2 January 19th 05 09:49 AM
Defined range difficulty Pat Excel Discussion (Misc queries) 7 January 16th 05 10:52 PM
named range refers to: in a chart Spencer Hutton Excel Discussion (Misc queries) 1 December 14th 04 11:15 PM
range names Pedro Excel Worksheet Functions 1 November 9th 04 07:27 PM
range names Pedro Excel Worksheet Functions 0 November 9th 04 07:26 PM


All times are GMT +1. The time now is 01:03 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"