One to get you thinking.

[Ross]

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

[Max]

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

[jetted]

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

[Franz Verga]

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

[Franz Verga]

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

[Ross]

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?

[Max]

> 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?