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
So I used Excel Magic Trick 185 to dynamically pull content from a spreadsheet: http://www.youtube.com/watch?v=6bGKhbUYOas
However,
It is only working for a few of my columns though and I am getting reference errors, http://www.mediafire.com/?gje8srbp8x2j48w
Can anyone check into my spreadsheet and help me understand why I get these ref errors? Is it because I have spaces in the column names or within the cells? I know a macro is probably a better way to do this, or an auto-filter, but I need it to dynamically update. Any ideas on how to fix this problem or a better way to solve it?
Thanks!
When you are making a formula say
=A5 + A6+A7
when you are doing this in cell D423, it sometimes gets annoying to have to either type in A6 and A7 manually or to navigate to A6 and A7.
I know there is a key you can hold down after doing A5 that keeps you on A5 even after putting in the + sign, so that you don't have to navigate all the way back there.
I can't figure it out!
Does anyone know what this is?
Please help!!
Thanks,
Michael
Hello There,
I have another question for you all. What I am trying to do it extract unique data from a list that I have. I know that I can do this through filters but this is not what I want. What I have is a list that looks like this
shoes
shoes
shoes
shirt
shirt
shoes
shirt
pants
shirt
pants
hats
now what I am wondering with out using a macro or a filter if there is a way to use a formula to extract the unique data and end up with a list like this
shoes
shirt
pants
hats
Any help would be greatly appreciated.
Thanks
Residnt
I am creating a project management sheet producing nice Gantt Charts using Excel 2010.
For the summary report I need advise on extracting records using Excel formulas.
I do not want to use Excel menus, PivotTables or VBA.
INPUT TABLE:
Task No.
Tasktitle
Assist1
Assist2
Assist3
Deadline
1.1
Task A
Gary
Mary
John
01.02.2011
1.2
Task B
Steven
Peter
Michelle
02.03.2011
2.1
Task C
Michelle
Gary
Kevin
03.04.2011
2.2
Task D
Steven
Michelle
Peter
10.05.2011
NEED TWO THINGS:
1) Put all unique values for Assist1, Assist2 and Assist3 in a new one-column table and sort alphabetically. Result would be:
Gary
John
Kevin
Mary
Michelle
Peter
Steven
2) Group all occurrences of assistants according to assistant name. The result is put in a new table and sorted after Task No.:
Result is like this for Gary:
Assist1
Assist2
Assist3
Task No.
Tasktitle
Deadline
Gary
Mary
John
1.1
Task A
01.02.2011
Michelle
Gary
Kevin
2.1
Task C
03.04.2011
Result is like this for Michelle:
Assist1
Assist2
Assist3
Task No.
Tasktitle
Deadline
Steven
Peter
Michelle
1.2
Task A
02.03.2011
Michelle
Gary
Kevin
2.1
Task C
03.04.2011
Steven
Michelle
Peter
2.2
Task D
10.05.2011
Anybody have some good ideas?
Thanks in advance!
I need to extract records into a new table from a database that is sorted
alphabetically in the 5th column (E). The records needed are those
containing an "x" in column H. Column F is hidden; will that have an effect?
Just This Video For Fun
http://www.youtube.com/watch?v=0VvjrldlqI0
I call a SQL stored procedure in my VBA code to extract almost a million records for a test case to different workbooks based on the selcetion criteria the user made. They use these test cases to generate different scenarios to be able to test a system. I limit the amount of records written to a workbook because it already takes for ever to open one workbook with 65000 records. Currently I only got as far as one workbook then Excel or my server hangs. Is there a easier and faster way of extracting so many records to excel workbooks? And how can I make the loop more dynamic because the total records returned will each time be different based on the selection the user made. Sometimes 10 loops (650 000 records) might be enough, other times when a possible million records can be generate it will not be enough.
vba part of code where procedure is called:
StartRecords = 0
EndRecords = 65000
For Mycounter = 1 To 10 'Should be more dynamic and not fixed to 10
SecondWhereString = ""
SecondWhereString = " WHERE ROWID > " & StartRecords & " AND ROWID
I have an existing table with multiple entries. each has a status code "1" or
"2". ow do I create/extract into a new table only those records with a staus
code of "1".
