|
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 |
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 |
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 |
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 ---- |
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 |
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 |
| All times are GMT +1. The time now is 12:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com