Forum Discussion
Comparing and getting a result in a column based on another table
Aditya Jadhav I notice you started using PQ but I could not really see what you were doing as you connected to a file on your local system. Tried to replicate what I think you want to achieve. The end result is in columns K:M where column M contains the references to the highest and lowest Aquifier code for that particular range
See attached.
- Yes that is it. This is what is needed. If you don't mind can you please guide me step by step how you did this as I'm getting confused with the Sheet. Just cannot relate what all you did to get the final outcome.
Aditya Jadhav Well, PQ is very powerful, but not always very easy. First I connect to the two tables. I call them TopBottom and Aqui. From the Aqui table I extract a list of only the aquifer names "AquiList", an I add a few extra columns to it that are needed later (Source and Index). By the way, I also added an Index column to the TopBottom table so that you can sort the outcome in the same order as your original table.
The Append query joins the two tables, filters, duplicates one column. Then it sorts Geo_Top and fills down the Aquifer column. Then it sorts the Geo_Bottom column and fills up the Aquifer - Copy column.
What comes next is a series of steps to determine the first and the last Aquifer code in the range (top to bottom) and then select all of the ones in between from the AquiList with the List.Range function. This part is probably subject to improvement by the hands of a real PQ master.
The last three steps are just to cleaning up a bit.