Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
One to get you thinking.
Hello, I have a table of data that has dates, production lines and production volumes (table one): Production Line Date Volume A02 13/3/06 189 C32 13/3/06 325 T12 14/3/06 21 The table contains data retrieved from the beginning of the year so there is a substantial amount, I have on a separate sheet a list of further data (table two): Production Line Date Tank AD02 13/3/06 A CD32 13/3/06 C TD12 14/3/06 B I want to use a formula that references two cells ( Production line and date in the first table) and returns from the second table the tank that the particular line was flowing into on that date. You will also notice an additional problem between the two table is that table one Production Line reads "A02" and table two's Production Line reads "AD02" so obviously this formula will have to ignore the additional D. Someone has suggested an array formula but seeing as I am using this formula possible several thousand times on one sheet it is slowing the whole workbook down far too much. Thanks for any help in advance. P |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
One to get you thinking.
Assume table one is in a sheet: X, data in cols A to C from row2 down
table two is in a sheet: Y, data in cols A to C from row2 down to say row1000 In X, Put in D2, array-enter (press CTRL+SHIFT+ENTER): =INDEX(Y!$C$2:$C$1000,MATCH(1,(SUBSTITUTE(Y!$A$2:$ A$1000,"D","")=A2)*(Y!$B$2:$B$1000=B2),0)) Copy D2 down as far as required Adapt the ranges to suit. Use the smallest range size sufficient to cover the max expected extent of data in Y. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Ross" wrote: I have a table of data that has dates, production lines and production volumes (table one): Production Line Date Volume A02 13/3/06 189 C32 13/3/06 325 T12 14/3/06 21 The table contains data retrieved from the beginning of the year so there is a substantial amount, I have on a separate sheet a list of further data (table two): Production Line Date Tank AD02 13/3/06 A CD32 13/3/06 C TD12 14/3/06 B I want to use a formula that references two cells ( Production line and date in the first table) and returns from the second table the tank that the particular line was flowing into on that date. You will also notice an additional problem between the two table is that table one Production Line reads "A02" and table two's Production Line reads "AD02" so obviously this formula will have to ignore the additional D. Someone has suggested an array formula but seeing as I am using this formula possible several thousand times on one sheet it is slowing the whole workbook down far too much. Thanks for any help in advance. P |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
One to get you thinking.
Hi You could try this code (see attach document) Sub copy_test() Sheets("Sheet1").Select rowcount = Cells(Cells.Rows.Count, "a").End(xlUp).Row For i = 2 To rowcount Sheets("Sheet1").Select Range("a" & i).Select first_value = ActiveCell.Value first_value_mod_1 = Left(first_value, 1) first_value_mod_2 = Right(first_value, 2) value_search = first_value_mod_1 & "?" & first_value_mod_2 Range("b" & i).Select second_value = ActiveCell.Value Sheets("Sheet2").Select Cells.Find(What:=value_search, After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False).Activate first_value_2 = ActiveCell.Value ActiveCell.Offset(0, 2).Select third_value = ActiveCell.Value Sheets("Sheet3").Select rowcount = Cells(Cells.Rows.Count, "a").End(xlUp).Row Range("a" & rowcount + 1).Select ActiveCell = first_value ActiveCell.Offset(0, 1).Select ActiveCell = first_value_2 ActiveCell.Offset(0, 1).Select ActiveCell = second_value ActiveCell.Offset(0, 1).Select ActiveCell = third_value Next End Sub +-------------------------------------------------------------------+ |Filename: Book4.zip | |Download: http://www.excelforum.com/attachment.php?postid=5025 | +-------------------------------------------------------------------+ -- jetted ------------------------------------------------------------------------ jetted's Profile: http://www.excelforum.com/member.php...o&userid=17532 View this thread: http://www.excelforum.com/showthread...hreadid=561064 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
One to get you thinking.
Ross wrote:
Hello, I have a table of data that has dates, production lines and production volumes (table one): Production Line Date Volume A02 13/3/06 189 C32 13/3/06 325 T12 14/3/06 21 The table contains data retrieved from the beginning of the year so there is a substantial amount, I have on a separate sheet a list of further data (table two): Production Line Date Tank AD02 13/3/06 A CD32 13/3/06 C TD12 14/3/06 B I want to use a formula that references two cells ( Production line and date in the first table) and returns from the second table the tank that the particular line was flowing into on that date. You will also notice an additional problem between the two table is that table one Production Line reads "A02" and table two's Production Line reads "AD02" so obviously this formula will have to ignore the additional D. Someone has suggested an array formula but seeing as I am using this formula possible several thousand times on one sheet it is slowing the whole workbook down far too much. Thanks for any help in advance. P Hi Ross, the only way without using VBA, AFAIK, is an array formula like this: =INDEX(Sheet2!C2:C4,,MATCH(A2,LEFT(Sheet2!A2:A4,1) &RIGHT(Sheet2!A2:A4,2),0)*MATCH(B2,Sheet2!B2:B4,0) ) Type (or copy and paste) the formula in cell D2 on your first sheet, assuming that the sheet with tank information is "Sheet2", press Ctrl+Shift+Enter, then copy down the formula. You could also try with VBA, but in this case I could not help you, because VBA it's not my strong point... -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
One to get you thinking.
Franz Verga wrote:
Ross wrote: Hello, I have a table of data that has dates, production lines and production volumes (table one): Production Line Date Volume A02 13/3/06 189 C32 13/3/06 325 T12 14/3/06 21 The table contains data retrieved from the beginning of the year so there is a substantial amount, I have on a separate sheet a list of further data (table two): Production Line Date Tank AD02 13/3/06 A CD32 13/3/06 C TD12 14/3/06 B I want to use a formula that references two cells ( Production line and date in the first table) and returns from the second table the tank that the particular line was flowing into on that date. You will also notice an additional problem between the two table is that table one Production Line reads "A02" and table two's Production Line reads "AD02" so obviously this formula will have to ignore the additional D. Someone has suggested an array formula but seeing as I am using this formula possible several thousand times on one sheet it is slowing the whole workbook down far too much. Thanks for any help in advance. P Hi Ross, the only way without using VBA, AFAIK, is an array formula like this: =INDEX(Sheet2!C2:C4,,MATCH(A2,LEFT(Sheet2!A2:A4,1) &RIGHT(Sheet2!A2:A4,2),0)*MATCH(B2,Sheet2!B2:B4,0) ) Type (or copy and paste) the formula in cell D2 on your first sheet, assuming that the sheet with tank information is "Sheet2", press Ctrl+Shift+Enter, then copy down the formula. Maybe this should be better: =INDEX(Foglio2!$C$2:$C$4,MATCH(1,(LEFT(Foglio2!$A$ 2:$A$4,1)&RIGHT(Foglio2!$A$2:$A$4,2)=A4)*(Foglio2! $B$2:$B$4=B4),0)) always array entered... -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
One to get you thinking.
Thanks, but€¦.
I want to keep away from array formulas as I have roughly 3400 lines of data when I refresh and taking 4.5 seconds a cell to calculate you can see how it crashes the system and I want to stay away from them. I want to avoid array's do you have any other options? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
One to get you thinking.
I want to avoid array's do you have any other options?
One non-array play could look something like this .. In sheet: Y (table two) Put in D2: =TRIM(SUBSTITUTE(A2,"D",""))&"_"&B2 Copy down Then in sheet: X (table one), Put in D2: =INDEX(Y!C:C,MATCH(TRIM(A2)&"_"&B2,Y!D:D,0)) Copy down Col D returns the required results -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Ross" wrote: Thanks, but€¦. I want to keep away from array formulas as I have roughly 3400 lines of data when I refresh and taking 4.5 seconds a cell to calculate you can see how it crashes the system and I want to stay away from them. I want to avoid array's do you have any other options? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Critical thinking puzzle | Excel Discussion (Misc queries) | |||
mastery level thinking | Excel Worksheet Functions | |||
stop auto fill from thinking for itself!!!!!!! | New Users to Excel | |||
How to I use ** without Excel thinking I want to type a formula? | Excel Discussion (Misc queries) |