Extract list using functions

Hi, can anyone help with the following:

I want to extract items in a list and show them in another worksheet. An 
example of the master list would be:

Name            Dollars    Quantity    Months
Customer1    $               Q            12
Customer2    $               Q            12
Customer3    $               Q             6

I want to be able to extract all those customers which have less than 
12months data (as indicated in the month column). I want to be able to do 
this using functions in another worksheet and list those seperately there.

Is there a way to do this?

Elijah 


0
Elijah_i (16)
11/8/2004 8:55:46 AM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
606 Views

Similar Articles

[PageSpeed] 6

Hi
you could use formulas but with lots of data (<300 recors) this gets quite 
slow and I would suggest to use 'Data - Filter' instead.

But here we go for one formula option (all formulas are antered as array 
formulas with CTRL+sHIFT+ENTER)
=INDEX('sheet1'$A$1:$A$100,SMALL(IF('sheet1'!$C$1:$C$100<12,ROW('sheet1'!$C$1:$C$100)),ROW(1:1)))
and copy this down as far as needed

"Elijah" wrote:

> Hi, can anyone help with the following:
> 
> I want to extract items in a list and show them in another worksheet. An 
> example of the master list would be:
> 
> Name            Dollars    Quantity    Months
> Customer1    $               Q            12
> Customer2    $               Q            12
> Customer3    $               Q             6
> 
> I want to be able to extract all those customers which have less than 
> 12months data (as indicated in the month column). I want to be able to do 
> this using functions in another worksheet and list those seperately there.
> 
> Is there a way to do this?
> 
> Elijah 
> 
> 
> 
0
frank.kabel (11126)
11/8/2004 9:24:03 AM
Let A2:D4 on Sheet1 house the sample you provided.

Sheet1 (Source)

In E1 enter: 0
In E2 enter: Count
In E3 enter & copy down:

=IF((A3<>"")*(D3<12),LOOKUP(9.99999999999999E+307,$E$1:E2)+1,"")

Sheet2 (Destination)

In A1 enter:

=LOOKUP(9.99999999999999E+307,Sheet1!E3:E5)

In A2 enter: Pos (Abbreviated from Position)
In A3 enter & copy down:

=IF(ROW()-ROW(A$3)+1<=$A$1,MATCH(ROW()-ROW(A$3)+1,Sheet1!$E$3:$E$5),"")

In B3 enter & copy across to E3 then down:

=IF(N($A3),INDEX(Sheet1!A$3:A$5,$A3),"")

Elijah Wrote:
> Hi, can anyone help with the following:
> 
> I want to extract items in a list and show them in another worksheet
> An
> example of the master list would be:
> 
> Name            Dollars    Quantity    Months
> Customer1    $               Q            12
> Customer2    $               Q            12
> Customer3    $               Q             6
> 
> I want to be able to extract all those customers which have less than
> 12months data (as indicated in the month column). I want to be able t
> do
> this using functions in another worksheet and list those seperatel
> there.
> 
> Is there a way to do this?
> 
> Elija

--
Aladin Akyure
-----------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=416
View this thread: http://www.excelforum.com/showthread.php?threadid=27616

0
11/8/2004 9:26:30 AM
Hi Frank,

How about just extracting the customers name - then I could use a standard 
vlookup to extract all other details. Would I need to use a similar formula?

Elijah


"Frank Kabel" <frank.kabel@freenet.de> wrote in message 
news:298A81C5-9C24-4841-84E3-F19A2FDEB3D5@microsoft.com...
> Hi
> you could use formulas but with lots of data (<300 recors) this gets quite
> slow and I would suggest to use 'Data - Filter' instead.
>
> But here we go for one formula option (all formulas are antered as array
> formulas with CTRL+sHIFT+ENTER):
> =INDEX('sheet1'$A$1:$A$100,SMALL(IF('sheet1'!$C$1:$C$100<12,ROW('sheet1'!$C$1:$C$100)),ROW(1:1)))
> and copy this down as far as needed
>
> "Elijah" wrote:
>
>> Hi, can anyone help with the following:
>>
>> I want to extract items in a list and show them in another worksheet. An
>> example of the master list would be:
>>
>> Name            Dollars    Quantity    Months
>> Customer1    $               Q            12
>> Customer2    $               Q            12
>> Customer3    $               Q             6
>>
>> I want to be able to extract all those customers which have less than
>> 12months data (as indicated in the month column). I want to be able to do
>> this using functions in another worksheet and list those seperately 
>> there.
>>
>> Is there a way to do this?
>>
>> Elijah
>>
>>
>> 


0
Elijah_i (16)
11/8/2004 9:38:14 AM
Hi
the first formula just does this. extracts all customer names which fullfill 
your criteria. VLOOKUP then could be a little bit faster but again: This kind 
of array formulas are getting slow for >300 records

"Elijah" wrote:

> Hi Frank,
> 
> How about just extracting the customers name - then I could use a standard 
> vlookup to extract all other details. Would I need to use a similar formula?
> 
> Elijah
> 
> 
> "Frank Kabel" <frank.kabel@freenet.de> wrote in message 
> news:298A81C5-9C24-4841-84E3-F19A2FDEB3D5@microsoft.com...
> > Hi
> > you could use formulas but with lots of data (<300 recors) this gets quite
> > slow and I would suggest to use 'Data - Filter' instead.
> >
> > But here we go for one formula option (all formulas are antered as array
> > formulas with CTRL+sHIFT+ENTER):
> > =INDEX('sheet1'$A$1:$A$100,SMALL(IF('sheet1'!$C$1:$C$100<12,ROW('sheet1'!$C$1:$C$100)),ROW(1:1)))
> > and copy this down as far as needed
> >
> > "Elijah" wrote:
> >
> >> Hi, can anyone help with the following:
> >>
> >> I want to extract items in a list and show them in another worksheet. An
> >> example of the master list would be:
> >>
> >> Name            Dollars    Quantity    Months
> >> Customer1    $               Q            12
> >> Customer2    $               Q            12
> >> Customer3    $               Q             6
> >>
> >> I want to be able to extract all those customers which have less than
> >> 12months data (as indicated in the month column). I want to be able to do
> >> this using functions in another worksheet and list those seperately 
> >> there.
> >>
> >> Is there a way to do this?
> >>
> >> Elijah
> >>
> >>
> >> 
> 
> 
> 
0
frank.kabel (11126)
11/8/2004 10:02:03 AM
Hi Aladin
I like your approach (and it probably much faster than my one-cell solution).
Just some curious questions

[...]
> =IF((A3<>"")*(D3<12),LOOKUP(9.99999999999999E+307,$E$1:E2)+1,"")

I would assume that the following is faster (not tested though):
=IF((A3<>"")*(D3<12),MAX($E$1:E2)+1,"")


> In A1 enter:
> =LOOKUP(9.99999999999999E+307,Sheet1!E3:E5)
If I got your description right woulnd't
=MAX(Sheet1!E3:E5)
return the same?

As said: Not tested from my side - just curious :-))
Frank
0
frank.kabel (11126)
11/8/2004 10:06:08 AM
Thanks Frank, Aladin

Just tried your function and it worked. I don't have a very large list about 
200 rows so it wasn't a problem.

Out of interest - could you tell me how modify the function if I were to use 
another criteria - say to pickup all customers which show 6 months?

Elijah

"Frank Kabel" <frank.kabel@freenet.de> wrote in message 
news:7FD21011-1014-43C3-B8E7-57D1EC089A54@microsoft.com...
> Hi
> the first formula just does this. extracts all customer names which 
> fullfill
> your criteria. VLOOKUP then could be a little bit faster but again: This 
> kind
> of array formulas are getting slow for >300 records
>
> "Elijah" wrote:
>
>> Hi Frank,
>>
>> How about just extracting the customers name - then I could use a 
>> standard
>> vlookup to extract all other details. Would I need to use a similar 
>> formula?
>>
>> Elijah
>>
>>
>> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
>> news:298A81C5-9C24-4841-84E3-F19A2FDEB3D5@microsoft.com...
>> > Hi
>> > you could use formulas but with lots of data (<300 recors) this gets 
>> > quite
>> > slow and I would suggest to use 'Data - Filter' instead.
>> >
>> > But here we go for one formula option (all formulas are antered as 
>> > array
>> > formulas with CTRL+sHIFT+ENTER):
>> > =INDEX('sheet1'$A$1:$A$100,SMALL(IF('sheet1'!$C$1:$C$100<12,ROW('sheet1'!$C$1:$C$100)),ROW(1:1)))
>> > and copy this down as far as needed
>> >
>> > "Elijah" wrote:
>> >
>> >> Hi, can anyone help with the following:
>> >>
>> >> I want to extract items in a list and show them in another worksheet. 
>> >> An
>> >> example of the master list would be:
>> >>
>> >> Name            Dollars    Quantity    Months
>> >> Customer1    $               Q            12
>> >> Customer2    $               Q            12
>> >> Customer3    $               Q             6
>> >>
>> >> I want to be able to extract all those customers which have less than
>> >> 12months data (as indicated in the month column). I want to be able to 
>> >> do
>> >> this using functions in another worksheet and list those seperately
>> >> there.
>> >>
>> >> Is there a way to do this?
>> >>
>> >> Elijah
>> >>
>> >>
>> >>
>>
>>
>> 


0
Elijah_i (16)
11/8/2004 11:35:52 AM
Elijah Wrote:
> Out of interest - could you tell me how modify the function if I were t
> use
> another criteria - say to pickup all customers which show 6 months?

Just change the <12 bit to =6 ...

=INDEX('sheet1'$A$1:$A$100,SMALL(IF('sheet1'!$C$1:
$C$100=6,ROW('sheet1'!$C$1:$C$100)),ROW(1:1)))

Hope this helps

--
Domeni
-----------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1078
View this thread: http://www.excelforum.com/showthread.php?threadid=27616

0
11/8/2004 12:53:39 PM
Domenic <Domenic.1fel5z@excelforum-nospam.com> wrote in message news:<Domenic.1fel5z@excelforum-nospam.com>...
> Elijah Wrote: 
> > Out of interest - could you tell me how modify the function if I were to
> > use
> > another criteria - say to pickup all customers which show 6 months?
> 
> Just change the <12 bit to =6 ...
> 
> =INDEX('sheet1'$A$1:$A$100,SMALL(IF('sheet1'!$C$1: 
> $C$100=6,ROW('sheet1'!$C$1:$C$100)),ROW(1:1)))
> 
> Hope this helps!

Thanks..Previously when I tried this it didn't work - now when I enter
it - it picks the customer up. Must be the good ol Ctrl-Shift-Enter
thing.
0
Elijah_i (16)
11/9/2004 12:30:22 AM
Reply:

Similar Artilces:

What's happened to the findfirst function in Access 2010?
I have a working Access 2007 Application which is now failing miserably in 2010. It hinges on location the first available working document in a table runing the following VBA code: With Me.Recordset .FindFirst "([fld1] + [fld2]) = 0" If .NoMatch Then .FindLast "([fdl1] + [fld2]) <> 0" Exit Sub End If .... FURTHER PROCESSING ... What must I do to correct this? Go through a record by record search? End With In 2007, this works correctly, stopping at the appropriate record (approc. rec 1385 in the recordset...

Using mouse wheel in VB editor
Just tried using the mouse wheel to scroll within visual basic editor in excel but it wont let me. How can I turn this feature on. I am using Excel 2003. Thanks in advance. This is a known problem with the latest MS mouse drivers. You can either install version 4 of the drivers (current version is 5), or use FreeWheel, and freeware program at http://www.geocities.com/SiliconValley/2060/freewheel.html . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "fullers" <fullers@discussions.microsoft.com> wrote in message ne...

Send to certain person using specific email account
I send an email to a certain receipient but don't want to use the default email account. Is it possible that when I send to them (plain text) , that it will choose the other account I want to use to send it? Please advise how I set this up. Thanks. I do not believe that this can be achieved automatically through standard use of Outlook as you need to manually specify the name of the account to send from if different than the default. However, it would be possible using the Outlook object model. An Addin would need to be developed that monitored each mail item prior to sending. If the d...

Macro for Sharepoint List and another Table?? Same # records?
I am creating a database which we have a form made up of prepopulated data (from a sharepoint list) and also data input needed by a user (for example, comments, etc.). I have two tables: one sharepoint list that is linked to sharepoint website and another table that houses the additional user inputs [table 2]. I have linked the files based on the primary key. So, every record in my table 2 has a plus sign and it will expand and you will see the linked data from the sharepoint list. Is there a way that I can automatically create a record for every corresponding record ...

Use exchange from a trusted domain
Hi folks, I have domain a and domain b. Domain a is all set upp with exchange etc. Domain b was bought, and they don't have exchange. I have trust between the domains and file sharing and authentication are ok. What we want to do is, make users on domain b and let them use the exchange (on a seperate storage) on domain a. The users on domain b must logon to domain b, but need to have mailbox on domain a. Domain a are running on win2003 and exchange 2003. Domain are running on win2003 with forest and domain ready for exchange. Any advices? thanks in advance, - Bjarni Hi, Thi...

Qrp Date functions
Where do I find the various functions to modify the Reports like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others and what they mean???? Barry Found the information at MSDN Transact-SQL Reference Barry "Barry L" <barryl@eryanjewelers.com> wrote in message news:usd3uP1CIHA.1188@TK2MSFTNGP04.phx.gbl... > Where do I find the various functions to modify the Reports > like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others > and what they mean???? > > Barry > ...

Comments to print on invoices and packing lists
We have a client with a specific need for printing customer comments and item comments: 1) They want to store comments on Customer Master. When a sales order is entered these comments should default on the sales order with an option to change. They want to mark these comments to be printed on sales order, invoice, packing list and pick ticket. 2) Items are stored with item specific comments. These comments need to come to the sales order when the order is entered with an option to change. They want to setup comments with similar setup where they print on order, invoice, pick ticket and packi...

Using Access 2003 on client to see Sharepoint 2007
Testing a Sharepoint environment and I can easily manipulate and run the database from Access 2007, but when I try to use Access 2003 it wants me to save the database to a location. The Access 2003 has the compatiblity pack, but it seems to not work when I download it. So the question is using Sharepoint 2007 and posting the Access 2007 format to it, can a user go to the sharepoint site and use a 2003 Access database that has the compatibility pack and open without having to download it? -- Message posted via http://www.accessmonster.com Send check for $500.00 to: David A Jenn...

Some Excel formatting functions taking a long time to work #2
Excel 2000 SP3 When I use some formatting functions for the first time in a session, such as bold, increasing font size etc, it takes up to 30 seconds to work. Meantime Excel is locked up until it completes that formatting call. I suspect faulty DLL? Has anyone experienced this? How to fix (other than a complete re- install) ? Any advice appreciated. Piri On Nov 5, 8:39=A0am, Piri <wiremu.pare...@hotmail.com> wrote: > Excel 2000 SP3 > When I use some formatting functions for the first time in a > session, such as bold, increasing font size etc, it takes =A0up to 30 > secon...

How Use ShellExecute with a CMemFile
Hello, I need to store files of various types (such as .doc, .xls, .jpg, .pdf, ..wmv, ...) in a database. Upon retrieval of such a file I have the file as data in memory in a CMemFile. I need to then "display" the file in the aproriate application. If I were to write the CMemFile to disk and create an actual file I could then, of course, use ShellExecute() to launch , say Word to display a .doc file. However, I would much prefer to avoid the overhead of writing the data to an actual file and then dealing with having to detect when the User is done and cleaning up the file I had ...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Adding a Macro to a VLookup Function
In Excel 2000 -- I would like to create a summary spreadsheet (sheet2). For each time that "Name" appears in sheet 1, row *, take information from the same row, but columns 2 and 6 and bring it over to sheet 2 in the designated area. I know that you need to do a "VLookup" function and I got that to work. I know that you also need a "Loop" statement, so that it will continue to perform the Vlookup and bring over the information for each time that the name appears. I don't know how to write the formula so that the VLookup and the Loop are combined. Examp...

Using INDIRECT within functions
I learned a bit from JLathman in a previous post but tried to use the same methodology with this formula (also learned from this Group to get the last entered value in a column) without much luck. The original formula was this with 2009 sheet being static: =LOOKUP(2,1/('2009'!H3:H65536<>""),'2009'!H3:H65536) Trying to make it dynamic I tried the following without success: =LOOKUP(INDIRECT("2,1/(" & O1 & "!$H$3:$H$65536<>"")," & O1 & "!$H$3:$H$65536)")) Did I miss something or are...

Date function quit working
Hi, I have an Access 2002 application that I have been running on Windows XP SP2 without issue. I just installed the application (running in Access Runtime) on a Windows Vista Home Premium machine. Now, anywhere I used the =Date() function, it fails and just shows #Name? I also have a subform on one of my forms that has now gone blank. It also uses the date function. I had this problem when I converted to Windows XP several years ago and updating the OWC10.dll to version 6619 fixed both issues. However, everything I have read says that reference file makes no difference to the Access...

total group & max function
I am using Access 2003 I created a Db for Machine Mtce. The table consists of 3 tables, (1) Basic Mach Infor (ie Mach#, Mach Name, Mfg etc) - (2) Operations Table - consists of different prev mtce (PM) that need to be completed at various time frames. Ex: "Mach 100" could have 3 different operations; Oper # 100A to change the oil due every 90 days, Oper # 100B to check the belts due every 180 days, Oper # 100C to grease the machine due every 365 days. The 3rd table is the "PM History" table which includes the history of all of the PM's completed with Work Order...

Function to determine Directory Size
I'd like to be able to determine the size of a directory or group of files. Does anyone know of an add-in that has a function whereby I can specify a path and get the size of directory of groups of files as the return value? Ex: =dirsize("C:\Documents and Settings\Bob\My Documents\School") =dirsize("C:\Documents and Settings\Bob\My Documents\School\*.doc") It would also be helpful if there were an option to include subdirectories. -- Thanks, Bob Chmara Argh.... Make that "get the size of a directory or group of files" Bob "Bob Chmara" fatfing...

Help with simple(?) VBA function
I'm trying to selectively BOLD cells by the use of a User-Defined function. No joy. The VBA Help topics suggest something like this: Function Bold() Worksheets("Sheet1").Range("A1:A5").Font.Bold = True End Function When I try to use it the referenced cells are not changed and the function returns "0". Can anyone point this VBA neophyte in the right direction? Thanks, -Dick- Hi Dick, A function can only return a value. Macros and Functions (Macros as Opposed to Functions) http://www.cpearson.com/excel/differen.htm If all...

Excel Text Function (Right, Left)
My sheet has columns like this A 1. 63mmx 4kg 2. 75mmx 4kg SWR How can i detect that if a1:a2 is right,3 "swr", that it should be returned 3 otherwise 6. How can i add =right function with conditional formula. Help me Rao Ratan singh Hi =IF(RIGHT(A1,"SWR"),3,6) fill down your column (move cursor over bottom right hand corner of the cell when you see a +, double click) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Rao Ratan Singh" <RaoRatanSingh@discussions.microsoft.com> wrote in message news:D...

How to use count for calls per hour
I am trying to create a pivot table and chart that will show me the # of calls per hour by day for a range of dates Aug 1 - Sept 19. My data looks like this where each line is the date and time of the call: How do I get a count of the # of the calls per hour by day? 8/1/08 12:48 AM 8/1/08 1:53 AM 8/1/08 2:36 AM 8/1/08 3:24 AM 8/1/08 3:37 AM 8/1/08 4:04 AM 8/1/08 4:44 AM 8/1/08 4:55 AM 8/1/08 4:58 AM 8/1/08 7:02 AM 8/1/08 7:28 AM 8/1/08 7:43 AM 8/1/08 7:47 AM 8/1/08 7:56 AM 8/1/08 7:58 AM 8/1/08 8:13 AM 8/1/08 8:33 AM 8/1/08 9:07 AM 8/1/08 10:28 AM 8/1/08 10:38 AM 8/1/08 10:44 AM 8/1/08 ...

How export all mailbox-enabled users from the GAL using Outlook?
Hello, We have a user which, for administrative reasons, needs to export all mailbox-enabled users in the GAL now and then. They just need all the names. Is there a straightforward way for them to do that? We don't want to give them any special permissions and want to avoid server scripting. Ideally, they should be able to export it to a CSV file or any text file. Thanks, - Alan. Alan wrote: > We have a user which, for administrative reasons, needs to export all > mailbox-enabled users in the GAL now and then. They just need all the > names. Is there a straightforward way fo...

Weekday function
Does VBA have a function which accepts an integer input between 1 and 7, which returns a string value to represent the corresponding day of the week?? For example, if you give the function an integer value of 1, then it should give back a string of "Sunday"... and the number 7 should return "Saturday". I know I can easily write my own function, but I'm curious if VBA provides a function already. No need for me to reinvent the wheel. thank you Don't think so, you would need to write one Function MyWeekDay(DayNum As Long) As String Dim Days A...

SQL 2008 running on a VM using all allocated memory
Hi, I've got a sql2008 server running on a VM. There's 9GB of physical RAM, which 7GB have been allocated to SQL Server. But when i look at task manager, i see that the SQL server is actually using all 7GB, which is pegging the memory usage of the overal box at above 90% used. We're mostly a sql2005 shop, and none of those servers are doing this. I have sql2005 running on VM's, someone actual servers as a named or default instance, and some even clustered. None of them have this problem. Is this a normal thing with sql2008 only? Any insight would be greatly appre...

Format List Box!
Can I format a list box which has a query,so if one of the fields called [Spelling] had the word "Spelling" it will change the colour of the font for that row Thanks for any help....Bob It's not possible with the native Access ListBox control. Instead, setup a Subform control to resemble a ListBox and use ConditionalFormatting to achieve the desired look. I think Arvin has a sample on his site here: http://www.datastrat.com/Download2.html -- HTH Stephen Lebans http://www.lebans.com Access Code, Tips and Tricks Please respond only to the newsgroups so everyone can ben...

List the UNIQUE certain fields from the database
Dear Experts, Please help related to the above subject. From this discussion, got the following formula which is work well and faster for small of database. =OFFSET($G$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE(DATA)=I$1:I1),ROW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1) where $G$1, the first row of DATA. how to convert it to VBA as this time i have a huge database. for Tab Data, Advanced, Unique Record only (works well also, but i need the VBA). basically we would like to do similar like this: Data Sample Vendor Name A B...

distibution list / updating
when we update our contacts how can we get outlook to update our distribution lists at the same time. What do you mean exactly? Can you give an example? Also what version of Outlook are you using? T. >-----Original Message----- >when we update our contacts how can we get outlook to >update our distribution lists at the same time. >. > That's what the Update Now button is for. -- Russ Valentine [MVP-Outlook] "wayne" <wayne@anandasacramento.org> wrote in message news:05ba01c3d4be$e83a5e80$a301280a@phx.gbl... > when we update our contacts how can we g...