Table manipulation in excel or any software that can solve this problem.

[kaquisado]

Good day everyone!

I have a problem and i cant solve it myself, trying my luck here if anybody had ever encountered this. I have a picture attached here and i want to convert table 1 to table 2. Is there anything in excel that can do this automatically since i have a total of 1206 different type of "table 1" and doing it manually will take me a whole lot of time.

Hope somebody find time to answer this. Thank you!

Table.jpg

[Pete_UK]

A picture is not much use to work on - attach an Excel file instead.

Pete

[kaquisado]

Attached here is an excel file of sample data. I want to reformat this table so that the top and bottom columns have increments of 1 while maintaining other columns in accordance with it. Basically from table 1 to table 2.

Please refer to the excel file attached. Thanks!

Table.xlsx

[Pete_UK]

I assume from your first post that you have far more data than in this example, and to produce over 1000 separate tables would be a bit cumbersome. So, I have given you the ability in the attached workbook to choose the borehole_ID from a drop-down in H1 (coloured yellow), and the appropriate table will be produced immediately for you. The drop-down source is the dynamic named range in column O, which lists the unique borehole_IDs from your raw data - as this is dynamic, you just need to add other IDs at the bottom of the list in any order.

Cell F1 contains zero (important - not a space or empty), and F2 contains this formula:

Please Login or Register  to view this content.

which is copied down BEYOND your data - the hyphens help to show how far it has been copied (I've copied to row 50).

I've used another helper column (H) with this formula in H2:

Please Login or Register  to view this content.

and I have coloured this grey, although you could make it white if you don't want to see those numbers. Then the table itself is made up of these formulae in the cells stated:

I2:

Please Login or Register  to view this content.

J2:

Please Login or Register  to view this content.

K2:

Please Login or Register  to view this content.

L2:

Please Login or Register  to view this content.

The formulae in H2:L2 then need to be copied down as far as to encompass your deepest borehole - I've copied to row 25 in the example file.

Now, all you need to do is to use the drop-down in H1 and choose a borehole of interest - the table will adjust automatically.

Hope this helps.

Pete

[kaquisado]

Wow this is so great! It'll help me a lot. Many thanks.