I have complete function disfunction

  • Thread starter Thread starter aresar
  • Start date Start date
A

aresar

I am trying to get certain data out of a list that cannot be put i
order. The numbers in Column A represent a Structure Number, and Colu
B is the depth of a pipe coming into and leaving that structure. Thi
is my list on sheet 1 and below on sheet 2 is where it applies.

Sheet 1
A B

1 6.89
2 6.25
3 5.89
4 4.26
5 3.65
6 2.02

3 5.25
8 4.90
9 2.26
10 1.87

3 4.68
5 3.74
7 0.25

Now my structures are listed in a different way on sheet 2 below. I
the function in Column C required a return for the Structure listed i
Column A & the function in Column D required a return for the Structur
listed in Column B. The answer for each structure is dependant on th
structures listed around it.

How would this work?

Sheet 2
A B C D

2 1 6.25 6.89
3 2 5.89 6.25
4 3 4.26 5.89
5 4 3.65 4.26
6 5 2.02 3.65

8 3 4.90 5.25
9 8 2.26 4.90
10 9 1.87 2.26

5 3 3.74 4.68
7 5 0.25 3.74

I know this is confusing, but I am completely stumped....Please help
 
Hi
it's maybe just me (getting late) but I don't understand the logic of
your resulting sheet. How are the results related, what is the
algorithmn, etc.
You may explain this logic with more detail
 
Name your data table MyTable (just makes it a bit easier to write the formula)
and then assuming your table on sheet2 starts in A2, then in cell C2 use the
following formula:-

=VLOOKUP(A2,MyTable,2,0)

Now copy the cell and just paste down your blank cells in C & D

Each formula takes the value from either column A or B, looks it up in Col A in
MyTable and then returns the corresponding value from the next Column, B.
 
Hi Frank - my take was that he now has two columns of data representing his
structure numbers on sheet2 instead of just one as he did on Sheet1. Against
these two columns of structure numbers A & B he needs to populate the next two
columns C & D with the respective values for each of those structure numbers
from the table on sheet1.
 
Hi Ken
that was also my first idea but he wants a result like
8 3 4.90 5.25
(his 6th example result)

Your formula would return
8 3 4.90 5.89

Problem is that the '3' appears more than once and I do not know which
one the OP wants to take??
 
LOL - OK, I see that now - and now that you have pointed it out, I think he
simply needs 3 VLOOKUP tables, as it appears to me now that he has 3 distinct
tables in his first sheet, and 3 distinct tables in his second.

Aresar - If I have called it right, now that Frank has pointed out the error of
my ways, then I think you can just name all 3 tables on sheet1 individually, eg
MyTable1, MyTable2, MyTable3, and then on sheet2 in Col C, assuming your first
structure number appears in cell A2, then use in say C2

=VLOOKUP(A2,MyTable1,2,0) and copy across and down alongside those structure
numbers for the first table only.

Assuming your structures for the second table start on sheet2 in A8, then in C8
use

=VLOOKUP(A2,MyTable2,2,0) and copy across and down alongside those structure
numbers for the second table only.

Assuming your structures for the second table start on sheet2 in A12, then in
C12 use

=VLOOKUP(A2,MyTable3,2,0) and copy across and down alongside those structure
numbers for the second table only.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



Frank Kabel said:
Hi Ken
that was also my first idea but he wants a result like
8 3 4.90 5.25
(his 6th example result)

Your formula would return
8 3 4.90 5.89

Problem is that the '3' appears more than once and I do not know which
one the OP wants to take??


--
Regards
Frank Kabel
Frankfurt, Germany

Ken said:
Hi Frank - my take was that he now has two columns of data
representing his structure numbers on sheet2 instead of just one as
he did on Sheet1. Against these two columns of structure numbers A &
B he needs to populate the next two columns C & D with the respective
values for each of those structure numbers from the table on sheet1.
Click to expand...
Click to expand...
 
That is exactly the problem I am having.

The 3 is repeated in both sheet 1 and sheet 2, but there is also set o
numbers to which it belongs (ex 3,8,9,10 are listed together in bot
sheets but in different fashion).

I have to associate the depth of the structure to the number listed o
the other sheet, but it is dependant on the structures around it.

I know there has to be some type of Match and Offset used so th
function can look to surrounding numbers to retrieve the data.

Please let me know if you need more data, or if I could email you fo
further assistance. Thank You
 
Hi Ken
you may be right if we interprete the blank rows in between correctly
:-)
Hopefully the OP will throw some light on this

--
Regards
Frank Kabel
Frankfurt, Germany

Ken said:
LOL - OK, I see that now - and now that you have pointed it out, I
think he simply needs 3 VLOOKUP tables, as it appears to me now that
he has 3 distinct tables in his first sheet, and 3 distinct tables in
his second.

Aresar - If I have called it right, now that Frank has pointed out
the error of my ways, then I think you can just name all 3 tables on
sheet1 individually, eg MyTable1, MyTable2, MyTable3, and then on
sheet2 in Col C, assuming your first structure number appears in cell
A2, then use in say C2

=VLOOKUP(A2,MyTable1,2,0) and copy across and down alongside those
structure numbers for the first table only.

Assuming your structures for the second table start on sheet2 in A8,
then in C8 use

=VLOOKUP(A2,MyTable2,2,0) and copy across and down alongside those
structure numbers for the second table only.

Assuming your structures for the second table start on sheet2 in A12,
then in C12 use

=VLOOKUP(A2,MyTable3,2,0) and copy across and down alongside those
structure numbers for the second table only.
Click to expand...
 
I can see how to associate them in seperate fashion, but each job I work
on the numbers will be totally different and change from time to time.
When I key in the data in Sheet 1 it would be a lot to keep changing my
functions to meet the "MyTable #' parameter.

I there a general function which can do the task I am looking for
without doing 'MyTable #'? The data from sheet 2 is exported from
another program into seperate sheets, and I have to manually import
from several sources into Sheet 2. This can change many times in both
order and resulting data during the course of a project and would be
too much to keep assigning table names.
 
Is your data on sheet1 always in blocks as your note would show -In which case
can you not just keep them separate and name them as I suggested as 3 tables.
 
As I stated last, the data in sheet 2 data can change mulitple time
during the course of a project, and is imported into sheet 2 fro
different locations. Sheet 1 can also change in order, but the orde
will always depend on the associations that come from the data liste
on sheet 2
 
Back
Top