find data based on depth ranges

J

justmcd

New member
Joined
Jan 3, 2012
Messages
39
Reaction score
0
Points
0
Good Day

I have data in sheet 1 which I want to sort in sheet 2 based on depth ranges, by giving chainage,diameter, trench width and depth. For example at chainage -370.59 diameter is 916,width is 2.1,depth is 0.34m and length is 0.0m. All this info must go to depth range 0-2m.

Your help will be highly appreciated
 

Attachments

  • QTY Remeasure.xls
    347 KB · Views: 12
Good evening,

Please see the attached file. I could only use the first line of formulas or the file would've been too big to upload. Highlight the formulas in row 5 and copy down. I believe the most prolific range had about 650 lines.

Best of luck,
 

Attachments

  • QTY Remeasure_sample.xls
    441 KB · Views: 21
Hi bgoree09

Thanks a lot, it works like a charm



bgoree09 said:
Good evening,

Please see the attached file. I could only use the first line of formulas or the file would've been too big to upload. Highlight the formulas in row 5 and copy down. I believe the most prolific range had about 650 lines.

Best of luck,
Click to expand...
 
Hi bgoree09

I have a different question by answering the same question.

If I only want to use same depth ranges but group for example all pipes with 916mm diameter in "Sheet 916" for different range i.e. 0-2m,2-3m, etc and do the same for "Sheet 816 and 610"
 

Attachments

  • QTY Remeasure Rev2 sample.xls
    487 KB · Views: 13
Hello again,

On "Flag 1" add the diameter to the beginning of the concatenate (ex: 9162-3m1)

Then add the pertinent number to the formulas on each page; ex:

=IFERROR(INDEX('Sheet 1'!$B:$B,MATCH("916"&'Sheet 2'!$A$3&ROW()-4,'Sheet 1'!$M:$M,0)),"")

Hope this helps,
 
Hi bgoree09

Thank you very much for your help. it works great

Regards
 
Hi bgoree09

I guess I celebrated too soon. When I try 610 and 816 it does not give me the correct data. I have tried retyping the formula several times but keep on getting incorrect data.

Regards​
 
Good morning,

Can you upload the file please?

Thanks,
 
Hi bgoree09

Thanks for you reply. I have delete some rows ancolumns to make file smaller. In a nutshell I have used the same formular from "Sheet 916" using cell B2 as reference instead of puting the diameter "916" in a formular. I have tried it on 916mm pipe and it works. When using the same formula for 816mm and 610mm. it bring the wrong data.

Thanks again for your help
 

Attachments

  • QTY Remeasure Rev5.xls
    474.5 KB · Views: 14
Hello again,

The revised file is attached. Flag 1 groups all of the data together into the different sections, then flag 2 numbers them in order of occurrence. When they are divided further into varying pipe sizes, the additional group has to go back into flag 1. When the 816 data started hitting, the grouping that was given was already down around the 200's in terms of uniqueness (so, when the pipe size was concatenated into Flag 3, the first occurrence was way down the list). Anyway, if you need any additional separations, just build them into the flag 1 field.

You're very welcome and have a good weekend,
 

Attachments

  • QTY Remeasure Rev5_sample.xls
    430.5 KB · Views: 13
Hi bgoree09

Thanks for you help, much appreciated.

bgoree09 said:
Hello again,

The revised file is attached. Flag 1 groups all of the data together into the different sections, then flag 2 numbers them in order of occurrence. When they are divided further into varying pipe sizes, the additional group has to go back into flag 1. When the 816 data started hitting, the grouping that was given was already down around the 200's in terms of uniqueness (so, when the pipe size was concatenated into Flag 3, the first occurrence was way down the list). Anyway, if you need any additional separations, just build them into the flag 1 field.

You're very welcome and have a good weekend,
Click to expand...
 
You must log in or register to reply here.
Back
Top