Hi,
I saw EXCELISFUN TRICK 369. I need to do something similar. I tried to follow his code and couldn't. Then I tried copying it and growing his records and still couldn't get it to work.
What I am trying to do is on the first list use an inventory list. Which could be about 2000 - 5000 records or maybe double that (not sure how big of a list I could use in Excel). But lets say it is the max number (if someone could tell me that number I would be most appreciative).
I then will load a second list, or would load a list into second column. I then want the difference (what is missing) from the second list to appear in the third list. If it can give me the row of where it is in the first list that would be great (not a problem if you can't). I just don't know why the code from that video is not working any help would be greatly aprreciated?
Thanks,
Peter Fraga
(fragapete@hotmail.com)
Hi All,
I was setting up a spreadsheet that was based on the following vid:
http://www.youtube.com/user/ExcelIsF...14/tqCEY5YMyqw
Dynamic sub tables based on a master sheet array formula
The formula in question is:
=IF(ROWS(A$7:A7)>$E$1,"",INDEX('2010Corn'!A$4:A$17,SMALL(IF(Table2[From]=$B$1,ROW(Table2[From])-ROW('2010Corn'!$H$4)+1),ROWS(A$7:A7))))
B1 is the customer I'm looking for, E1 is the count for the customer and the master page is 2010Corn. I have 20 sheets looking to this master page for data. It works great, except for an issue when adding a new line in the master table.
What is happening is when I get to the end of a row, I tab to enter a new line in the table. It takes up to a minute for the cell to change color and for me to regain control of the computer.
I have run a performance trace and while the computer is locked, one of the CPU core's is pegged for the whole time with a processor que of up to 10 items at a time.
My question is...does anyone have any hints how to optimize this formula?
Thanks
Tony
First I want to thank you for answering all my questions in such a timely manner. Here is my question for today. I have two lists. The first is a master list of all store numbers in the country. The second is a list of all existing accounts (store numbers). I would like to have a list of prospects extracted out of the master list (accounts that are not in the existing accounts list). Is there a way to tell the master list to only show me the numbers that are not existing accounts?
Just This Video For Fun
http://www.youtube.com/watch?v=0VvjrldlqI0
I have a spreadsheet that lists every sales order for an entire year. The list contains orders from all customers, with the same customers often buying on different occasions. I want to be able to run a report/macro that totals up the business from each customer for the year so that I can determine the top 15 customers for the year.
Here is my spreadsheet for January, it runs down thru December ending at Row 241, please advise:
A
B
C
D
1
Order Date
Customer
State
Order Amount
2
1/3/2008
ATT SOUTHEAST
TN
$ 3,465.00
3
1/3/2008
ATT SOUTHEAST
TN
$ 5,775.00
4
1/8/2008
ATT SOUTHEAST
TN
$ 280.00
5
1/8/2008
DELTA TEL
MS
$ 9,070.00
6
1/8/2008
DELTA TEL
MS
$ 21,465.00
7
1/8/2008
SERVIDINAMICA
FL
$ 1,195.00
8
1/10/2008
ATT SOUTHEAST
KY
$ 1,155.00
9
1/11/2008
QWEST
GA
$ 3,344.72
10
1/11/2008
GB NET
FL
$ 9,710.00
11
1/15/2008
GB NET
FL
$ 9,710.00
12
1/15/2008
EATEL
LA
$ 1,670.00
13
1/17/2008
KAPLAN TEL
LA
$ (1,500.00)
14
1/17/2008
RESTOR TELECOM
FL
$ 20,240.00
15
1/22/2008
ATT SOUTHEAST
TN
$ 1,155.00
16
1/22/2008
ATT SOUTHEAST
TN
$ 1,155.00
17
1/23/2008
SIEMENS
NC
$ 1,200.00
18
1/24/2008
ATT SOUTHEAST
LA
$ 1,155.00
19
1/24/2008
ATT SOUTHEAST
AL
$ 985.00
20
1/24/2008
ATT SOUTHEAST
TN
$ 1,155.00
21
1/24/2008
ATT SOUTHEAST
TN
$ 2,310.00
22
1/24/2008
ATT SOUTHEAST
GA
$ 1,155.00
23
1/24/2008
ATT SOUTHEAST
GA
$ 1,155.00
24
1/24/2008
TRILOGY
FL
$ 22,030.00
25
1/28/2008
ATT SOUTHEAST
TN
$ 978.00
26
1/28/2008
ENGINEERED IDEAS
LA
$ 1,775.00
27
1/29/2008
ATT SOUTHEAST
GA
$ 9,240.00
28
1/29/2008
ALLTEL
FL
$ 1,150.00
29
1/29/2008
TRIAGE PARTNERS
FL
$ 10,900.00
30
1/29/2008
VOLT TELECOM
FL
$ 109,000.00
31
1/30/2008
HIGHLAND TELEPHONE
TN
$ 7,480.00
32
1/31/2008
ATT SOUTHEAST
GA
$ 1,105.00
33
1/31/2008
ATT SOUTHEAST
MS
$ 3,675.00
34
1/31/2008
ATT SOUTHEAST
AL
$ 1,225.00
35
1/31/2008
ATT SOUTHEAST
AL
$ 4,900.00
36
1/31/2008
ATT SOUTHEAST
MS
$ 3,675.00
37
1/31/2008
ATT SOUTHEAST
LA
$ 1,105.00
[IMG]file:///C:/Users/Tony%20Bracci/Documents/EJHTMLe/jeanie.gif[/IMG]
[IMG]file:///C:/Users/Tony%20Bracci/Documents/EJHTMLe/jeanie.gif[/IMG]
Sheet1 named "Alignment" (contains alignment of our sales people):
---------------------------------------------------------------------------
Column A= Customer List of ID's
Column B= Salesperson 1 ("yes" in cells below if he has that customer)
Column C= Salesperson 2 ("yes" in cells below if he has that customer)
Column D= Salesperson 3 ("yes" in cells below if he has that customer)
Sheet2 named "Customer Listing":
---------------------------------------
Cell A1= drop-down selectable list of our sales people
When a salesperson is selected I need a list of all their customers to appear on the sheet.
I would prefer to do this with formulas rather than code. I remember reading somewhere a while back that with dynamic ranges and maybe the Offset(?) function this could be done formulaically. Is that possible?
How do I show clients that have more than 6 orders? I have a table filled with orders but only want to have a list clients that had 6 or more orders? i have a customer number for each client. For example:
Customer# | Product | Dollar Amount | Date of order
thanks
Hello
I have many products and many customers which I would like to display in a report. But I would like to highlight the 3 main customers. maybe even in a different color (like main customer dark green, second best in medium green, and third best in light green)
here is an example of the list:
Customer Value
A 4
B 40
C 20
D 100
E 30
F 8
I know i can do it with sorting or filterin. But I want to keep the list as it is.
so in this case the Customer D would be dark green, Customer B medium und Customer E light green.
How is this possible?!?
Thanks in advance.
I have a worksheet named Clients where I have entered Customers Name, Address, Suburb etc. On the quote sheet I have created a list box for clients name cell C15. When I select Clients name the address suburb etc should populate the cells C16 : C20 but I can't get it too work.
Also when I click on the category list box and product list box which is dependent on what is selected in the category list box they don't appear i have to scroll up to find them
I have included my spreadsheet
Thanks
I am looking to set up a data base of 100 customers. I need to be able to
pull the data from the spreed sheet to make lables and to do a mail merge
letter. What would be the best way to set that up useing word and excell
