"L" plate driver needs guidance

Hi all,

Is it possible in a VLookup situation
to extract the "second" occurance of a match rather than the first?

I extract details of a first match, from a list of names/expenditure
lets say a customer spent $1
now I want to find out how much the customer spent on
a previous visit, other than that matched lookup.

the customer / expense is in a table 61,000 records long

Thank you.



0
rodney (31)
10/28/2004 4:39:29 AM
excel 39879 articles. 2 followers. Follow

6 Replies
386 Views

Similar Articles

[PageSpeed] 1

Hi
try the array formula (entered with CTRL+SHIFT+ENTER):
=INDEX(B1:B60000,SMALL(IF(A1:A60000="lookup_value",ROW(A1:A60000)),2))

Note: If you really have >60K records you may consider using something
else than Excel (e.g. a real database)

--
Regards
Frank Kabel
Frankfurt, Germany

"Rodney" <rodney@touch88.com.au> schrieb im Newsbeitrag
news:O#am8fKvEHA.1300@TK2MSFTNGP14.phx.gbl...
> Hi all,
>
> Is it possible in a VLookup situation
> to extract the "second" occurance of a match rather than the first?
>
> I extract details of a first match, from a list of names/expenditure
> lets say a customer spent $1
> now I want to find out how much the customer spent on
> a previous visit, other than that matched lookup.
>
> the customer / expense is in a table 61,000 records long
>
> Thank you.
>
>
>

0
frank.kabel (11126)
10/28/2004 6:07:21 AM
Thank you Frank.

| Note: If you really have >60K records you may consider using something
| else than Excel (e.g. a real database)


"if you really..........."
What!   do people tell lies on this NG?

Actually I have 4 million records in VFP, but I dump a sub set down
to excel for "what if" scenarios. Excel will only take 65,000
so I need some space for the reference list.

I am unable to visualise how using a database could sort my queries
In fact I cross over from database to do the lookup.
How else could I refer say 800 names in a lookup scenario in a database.

I <do> appreciate your response

Rod



0
rodney (31)
10/28/2004 7:27:14 AM
I was unable to manufacture a result, based on your
suggestion Frank.
My current formula reads:

=IF(ISERROR(VLOOKUP(B61726,$B$2:$E$61721,4,FALSE)),"/",(VLOOKUP(B61726,$B$2:$E$61721,4,FALSE)))

Are you able to suggest anything based on that please?





| Hi
| try the array formula (entered with CTRL+SHIFT+ENTER):
| =INDEX(B1:B60000,SMALL(IF(A1:A60000="lookup_value",ROW(A1:A60000)),2))
|
| Note: If you really have >60K records you may consider using something
| else than Excel (e.g. a real database)
|
| --
| Regards
| Frank Kabel
| Frankfurt, Germany



0
rodney (31)
10/28/2004 2:51:50 PM
Hi
for this many data records Excel is definetely the wrong tool (at least
IMHO). For this consider using specialised tools. e.g.
- for Reporting: Crystal Enterprise or Business Objects
- For data mining specialised data mining tools
- as you're talking about customer data you may consider using a CRM
package. e.g. mySAP CRM, Siebel, Peoplesoft

Frank
P.S.: Don't know what VFP is :-))

--
Regards
Frank Kabel
Frankfurt, Germany

"Rodney" <rodney@touch88.com.au> schrieb im Newsbeitrag
news:Op2zr9LvEHA.2116@TK2MSFTNGP14.phx.gbl...
> Thank you Frank.
>
> | Note: If you really have >60K records you may consider using
something
> | else than Excel (e.g. a real database)
>
>
> "if you really..........."
> What!   do people tell lies on this NG?
>
> Actually I have 4 million records in VFP, but I dump a sub set down
> to excel for "what if" scenarios. Excel will only take 65,000
> so I need some space for the reference list.
>
> I am unable to visualise how using a database could sort my queries
> In fact I cross over from database to do the lookup.
> How else could I refer say 800 names in a lookup scenario in a
database.
>
> I <do> appreciate your response
>
> Rod
>
>
>

0
frank.kabel (11126)
10/28/2004 5:09:34 PM
Hi
this should work. What does not work exactly?

--
Regards
Frank Kabel
Frankfurt, Germany

"Rodney" <rodney@touch88.com.au> schrieb im Newsbeitrag
news:uaTRG2PvEHA.3080@TK2MSFTNGP12.phx.gbl...
> I was unable to manufacture a result, based on your
> suggestion Frank.
> My current formula reads:
>
>
=IF(ISERROR(VLOOKUP(B61726,$B$2:$E$61721,4,FALSE)),"/",(VLOOKUP(B61726,
$B$2:$E$61721,4,FALSE)))
>
> Are you able to suggest anything based on that please?
>
>
>
>
>
> | Hi
> | try the array formula (entered with CTRL+SHIFT+ENTER):
> |
=INDEX(B1:B60000,SMALL(IF(A1:A60000="lookup_value",ROW(A1:A60000)),2))
> |
> | Note: If you really have >60K records you may consider using
something
> | else than Excel (e.g. a real database)
> |
> | --
> | Regards
> | Frank Kabel
> | Frankfurt, Germany
>
>
>

0
frank.kabel (11126)
10/28/2004 5:10:13 PM
Oh!.... I'll have another try (read bumble) Frank,
VFP=Visual Fox Pro  Database.

Thankyou.




| Hi
| this should work. What does not work exactly?
|
| --
| Regards
| Frank Kabel
| Frankfurt, Germany
|
| "Rodney" <rodney@touch88.com.au> schrieb im Newsbeitrag
| news:uaTRG2PvEHA.3080@TK2MSFTNGP12.phx.gbl...
| > I was unable to manufacture a result, based on your
| > suggestion Frank.
| > My current formula reads:
| >
| >
| =IF(ISERROR(VLOOKUP(B61726,$B$2:$E$61721,4,FALSE)),"/",(VLOOKUP(B61726,
| $B$2:$E$61721,4,FALSE)))
| >
| > Are you able to suggest anything based on that please?
| >
| >
| >
| >
| >
| > | Hi
| > | try the array formula (entered with CTRL+SHIFT+ENTER):
| > |
| =INDEX(B1:B60000,SMALL(IF(A1:A60000="lookup_value",ROW(A1:A60000)),2))
| > |
| > | Note: If you really have >60K records you may consider using
| something
| > | else than Excel (e.g. a real database)
| > |
| > | --
| > | Regards
| > | Frank Kabel
| > | Frankfurt, Germany
| >
| >
| >
|


0
rodney (31)
10/28/2004 11:45:37 PM
Reply:

Similar Artilces:

need help getting direct deposit paycheck to work with spending & savings budget
I am using the trial version of Money Delux Plus 2008 to try out the Spending and Savings budget. During the setup of the budget it told me I had to enter my paycheck in advanced bills in order to track the gross income and deductions for this budget. I had my paystub and set that up; now I see it in Bills. This is the point where I need help. My paychecks are deposited directly into my bank account and the payee varies slightly each time due to it showing account numbers and transaction numbers etc..(ex. 0446 083107 160 ;DES=PAYROLL ;ID=0446 013...) in the payee name as well as the amount ...

NEED HELP
I posted this earlier but didn't see a response. We are having MAJOR speed problems with our CRM. With only 30 people using it and having the Web interface on 1 server (duel 3.2 XEON with 4GB RAM) and the SQL interface on another, it makes no sense that our CRM- based interface calls are DOG slow. I mean, executing an .Update on a contact can take 5-30 seconds. For our SRs, it can take a good 45 seconds to load a screen of 300 activities. It's like "uhhh..." Compound this with code that does multiple manipulations and we're in a world of hurt. Calls to the SQL...

need file for excel SBFM40.EXE
I am receiving a Visual Micro C+++ run time error on elcel and all other Office program. When excel trys to load it wants to see the file "SBFM40.XLA" that is missing or has been renamed. Can someone please e-mail me this file since I can't get it from my original disk. Any help to clear the runtime error would be most helpful. Tigerpaw51 wrote: > I am receiving a Visual Micro C+++ run time error on elcel and all > other Office program. When excel trys to load it wants to see the > file "SBFM40.XLA" that is missing or has been renamed. Can someone >...

Red dots between words (O/L 2003)
Someone here using O/L 2003 has suddenly started to get two minute red dots between each word when he creates a new mail message. This is not like "show/hide" as paragraph marks, tabs etc are not being revealed, yet every time he hits the space bar there appears two tiny little red dots for each space! Any clues, anyone please? I'm baffled.... Gill W Did you turn off the spaces or all marks in Word's tools, options, menu, view to verify it wasn't spaces? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for W...

Graph help. Experience needed.
I have a list of jobs my company has bid along with how much the bid amounts were and if we won them or not. I need a graph that states how many we bid and how many we won seperated in $5000 increments. Such as... 0-5000 range 5001 to 10000 range 10001- 15000 range and so on... Detailed instructions PLEASE!! On Tue, 16 Oct 2007 12:44:02 -0700, HoganD87 <HoganD87@discussions.microsoft.com> wrote: Using the Northwind sample application, OrderDetails table, I created a similar query returning the number of orderdetails in the range 0-100, 100-200, etc.: SELECT Count([Order Details]...

L/100 klms
I am trying to create a spreadsheet to help me work out the klms rating of my van. I drove 1933.33 klms, used 187.626 liters and the van should get 8.8/100 klms. I would like to be able to compare another vehicle against the van by entering it's ?/100 klms rating. Does anyone know how I can do this. Thanks Hi Dale With Kms in A1 and Liters in B1 enter in C1 =B1/(A1/100) -- Regards Roger Govier "Dale" <laingds@canada.com> wrote in message news:1161920366.083605.281630@m73g2000cwd.googlegroups.com... >I am trying to create a spreadsheet to help me work out the kl...

Help needed for upgrade from 3.0 to 4.0
Hi all, We're planning to upgrade from MS CRM 3.0 to 4.0. I would like to know if there is any useful literature to go through first. Steps, requirements, best practices, common errors others faced, demo... Anything that can be of use. We're on MS Outlook 2003, SQL Server 2000 and CRM Server installed on Windows Server 2003. Does anything else need to be upgraded first? Will this be seamless for users or will they have to change their systems, besides the CRM Outlook client obviously? Thanks, Mohamed Mohamed - There is a wealth of information on Microsoft and other sites related t...

Need some help BAD with conditional formatting
I have 2 sheets with infomation. Sheet1 C2:C15000 have some text in each cell. Some cells in C2:C15000 have the same text, or may have slightly different text. Sheet2 E3 has a free text cell that my end users can type into it at will. I like to turn the Cells C2:C15000 in Sheet1 to turn yellow if the keyword in E3 is found within the text in C2:C15000 on Sheet1. -- tech1NJ Put your cursor into Sheet 1 Cs. Go to Conditional Formula, and choose is equal to and then move to the next box and type in =$E3. The choose Format and choose yellow on the Patterns tab. Copy and paste t...

Driver update problem for raw PDO and stacks
Hi, I have a KMDF HID mini driver which creates a raw PDO. I have also install a UMDF driver on top of that raw PDO. So far everything works ok but I just found a problem. If I go to device manager, I can see both my HID device and my raw PDO device (raw PDO device is list as hidden device). But if I do a "Update driver software" on one of them, it will only update the driver for the one I selected but not the other one. BTW, I used one INF file for intalling/updating both drivers. Is there a way to force driver update on the one not selected? Or is there a way...

Maternity Clothes (L/XL)
I give him(her) 70 dlls ...

TFAT and NAND FMD block driver
Hello, I'm working on Windows CE 5.0 and Freescale BSP iMX31. I want to use TFAT on my NAND Flash device but Microsoft Documentation said that the block driver have to execute atomic sector writes. Where I can see if my NAND FMD driver executes atomic sector writes ? Thanks, Paolo On Nov 19, 9:13=A0am, paolo patierno <paolopatie...@discussions.microsoft.com> wrote: > Hello, > I'm working on Windows CE 5.0 and Freescale BSP iMX31. > I want to use TFAT on my NAND Flash device but Microsoft Documentation sa= id > that the block driver have to execute ato...

G/L Transaction Tables
Can someone point me to which tables house debit/credit g/l transactions? Thanks. Here you go Grieves - Transaction Work GL_TRX_HDR_WORK GL10000 Transaction Amounts Work GL_TRX_LINE_WORK GL10001 Transaction Clearing Amts Work GL_TRX_Clearing_WORK GL10002 Quick Journal Work GL_Business_Form_HDR_WORK GL10100 Quick Journal Amounts Work GL_Business_Form_LINE_WORK GL10101 Account Current Summary Mstr GL_Account_SUM_MSTR GL10110 Account Summary History GL_Account_SUM_HIST GL10111 General Ledger Tax Work glTaxWork ...

Workflow Monitor, Manager, Import... "Localhost" L
At Workflow Monitor, Workflow Manager, Workflow Import, Workflo Export "Localhost" Logon, it returns an error of "You do not have sufficient privileges to run the Workflow Monitor. For mor information, contact your system administrator". !!!!!!I'am my syste Administrator!!!!!!!!!!. What can I do Try to type the name of the CRM Web Site If it is http://crm ..... You should type just CRM with out the http Ricardo Le Roux CRM "Bartek" <bartek.stefanski@bicom-dot-pl.no-spam.invalid> wrote in message news:hvednQj2obkj65XfRVn_vg@giganews.com... > At...

Creating an Order Form/Invoice- Need Help
Hi. I'm fairly new to access. I've design a database that we'll use for automating our invoice process. We have numerous customers and each has it's own prices for our products. I created tables with relationships to hold this information. I broke my database into a frontend with forms, queries, etc and a backend with the tables. That's all working fine. I've run into trouble I have a products table, a price table. I created an order and an orderdetail table. I used the Northwind and the Access2000 Books databases as guides. In addition, we have special orders....

Need help extracting text from EDLs
I really am trying to learn this stuff, but I haven't programmed anything since my TRS-80 Basic days and I'm a bit overwhelmed. I want to extract text from Edit Decision Lists so that I have a list of shots used in a film. Here is an example of one event of an EDL: 002 TAPE004 V C 04:45:22:06 04:45:24:14 02:00:08:00 02:00:10:08 FROM CLIP NAME: 7C-4_B_ DLEDL: PATH: /raids/luc_1/bun/reel_02/oscans/1222/bun_7c_4_b_02/2048x1556/ The text that follows "FROM CLIP NAME:" is the name of the clip that I want to extract so that I have a document on onl...

need help with pivotchart on report?
Dear all, I have been trying, in vain, to find a way to my problem...if anyone can help I'll pay him a massive pint of Guiness: I have a car accidents database with a multicriteria research form. On the form there is a button opening a report with the only the search results. I need to integrate a pivotchart, a simle one, that would only use the forms results and not the entire data base. The chart is on a form named "frmtest" using an SQL statement from a global query + the a field with sum of dead and injured. When I stick the form into the report and link the master and chil...

l
sjfhflsj ...

re done Match formula needed
Sorry about the last post , here is an other copy in sheet R! A D F 1 num time price 2 1 10:25 st 90 3 2 11:00 st 190 4 3 11:35 st 80 5 4 12:00 st 110 6 5 1:00 st 110 7 6 1:20 st 90 8 ...

Help needed
Nothing happens when i try to open excel ...

I need a customized Excel chart: Column
I found the customized chart type "Line - Column with 2 axes," but I want 2 columns "Column - Column on 2 axes." Is this available or can I create it? Yes. Right click on the line chart and select chart type and change the chart type to a column chart. "S. Middendorf" wrote: > I found the customized chart type "Line - Column with 2 axes," but I want 2 > columns "Column - Column on 2 axes." Is this available or can I create it? http://peltiertech.com/Excel/Charts/ColumnsOnTwoAxes.html - Jon ------- Jon Peltier, Microsoft Excel M...

Need helps about importing and etc.
I'm a newbie, where can i find information about RMS Setup and Deployment Overview, Installation Requirements, Terminology, Upgrading, Configuration, Import/Export, Limitations, and so on? thanks, Jay K Partner Source http://www.microsoft.com/BusinessSolutions/partnersource.mspx -- Mobitech Lady Amy Luby Mobitech 402.330.0707 www.mobitechonline.com "JayK" <JayK@discussions.microsoft.com> wrote in message news:3AD41CE8-6B86-45A0-A2EA-A61A91CFB482@microsoft.com... > I'm a newbie, where can i find information about RMS Setup and Deployment > Overview, Instal...

S P A C E D O L L A R S
This is a multi-part message in MIME format. ------=_NextPart_000_0066_01C37624.8F26F0E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable -------------------------- -------------------------- alt.visa.us=20 -------------------------- -------------------------- Space Dollars P O Box 1354 Westport, Wa. 98595-6321 360-248-9643 http://www.spacedollars.com=20 info@spacedollars.com=20 -------------------------- -------------------------- America Online & Time Warner, 22000 Aol...

Populate Customer from Drivers License
Has anyone seen an addon that can populate a new customer based on the information from the 2d barcode on their drivers license? (I know about Newestech's People PoP that bases it on a phone number - but I have a client that is specifically looking for a drivers license addon.) I know RetailPro has it as an addon to their POS software, but I haven't see anything for RMS yet. Jared ...

Need help with PASTE_SPECIAL-VALUES
Is there a formula that allows you to copy an array of cells an paste-values these cells into a different array automatically? If no please read below... When importing large tables of numbers from the internet, I tried to d everything I knew to do to get Excel to recognize them as number (formatted the cells as numbers, multiplied the cells by 1, and =i cell>0,cell,"0" but none of these change its format. After importing data from the internet, it does not allow me to sor the cells unless I copy/paste-values it first. Maybe there is anothe way to also get around this; do yo...

need help outgoing mail getting delayed...
and some are getting sent back completely because it is taking too lon to send them. we have an exchange server and our guy can't figure i out. people are having trouble sending to certain emails. we ge delay errors from our exchange server and even some system admi messages saying the attempt to send has been rejected completel because it is taking too long, and these are small files too. from exchange server: "This is an automatically generated Delivery Status Notification. THIS IS A WARNING MESSAGE ONLY. YOU DO NOT NEED TO RESEND YOUR MESSAGE. Delivery to the following recip...