Hello All,



I have two worksheets that I need a formula or a function to merge data from both worksheets into a third.
What I am trying to do is merge data associated with a specific structure (e.g. a bridge); for example in the worksheet called Merged Sheets I have highlighted two examples (also highlighted in other worksheets) of what I am trying to achieve:
• Data sourced from the BARRIER_RCS worksheet will be a straight lookup (so I am OK with this).
• For a unique Plan Number use the specific ROAD NO and specific RRD (Road Running Distance) to find the road number in the CURR_LANE_WIDTHS worksheet then find where the exact structures RRD sits within the START RRD and END RRD and return the various Lane Widths.

BARRIER_RCS
This worksheet contains data relating to a range of structures.
• Column A contains the plan number of a structure on a road (unique ID).
• Column B is the Road Number that the structure is on (there can be multiple structures on a road).
• Column C has the Road Running Distance (RRD) location of that structure (each structure will have a unique RRD).

CURR_LANE_WIDTHS
This worksheet contains physical data related to a variety of roads, i.e.
• Column A has the Road Number (note: a road number can be repeated multiple times).
• Column B has a Start RRD for a variety of road data between two RRD’s.
• Column C has the End RRD for each segment of road.
• Column D has the road Lane Width of the left lane.
• Column E has the road Lane Width of the inner left lane (if the road has two lane travelling in the same direction).
• Column F has the road lane width on the right lane.
• Column G has the road Lane Width of the inner right lane (if the road has two lane travelling in the same direction).

I hope this all makes sense, but if not please ask me to clarify.

Regards,
Dave T

Attached Files

Merged worksheets - To be posted.xlsx (40.7 KB, 9 views)

Try Array-Entering (Ctrl+Shift+Enter, not just Enter) this formula in cell D2 of sheet Merged Sheets. Copy down and across.
It returns zero for where there are blank cells in CURR_LANE_WIDTHS, but you could wrap the whole formula in an IF statement to return a blank instead, if you're confident there are no lane widths of zero.

Hello p45cal,

I really, really appreciate your reply.

I had found a post on Ozgrid that was most likely another solution:
https://www.ozgrid.com/forum/forum/h...ss-than-checks
But I was getting results that did not appear to be quite correct.

I also found problems when further researching the SUMPRODUCT formula:
- the formula needed to be on the same BARRIER_RCS worksheet and not the Merged Sheets worksheet.
- that column lengths for the ranges needed to be the same

Here is the formula I modified based on the formula in the attached worksheet from the Ozgrid site.
For copying the formula across I needed to change the column ID in the formula and the column number in the INDIRECT("CURR_LANE_WIDTHS!D"&SUMPRODUCT part as well.

I also played with the array formula posted by mikerickson but could not get it to work.

I must admit when I looked at the formula from the Ozgrid post I did not expect a reply to this post, so I really do appreciate your reply.

Regards,
Dave T

The Ozgrid site appears down at the moment so I can't comment.
However: Does my offering give you the results you're looking for?

Hello p45cal,

Sorry about the delay in getting back to you, but I thought I would re-run both options on a larger data set to see both options and compare the results.

The worksheet called Merged Sheets has your array formula in columns D to G.
The worksheet called BARRIER_RCS has the formula from the Ozgrid site.

In both worksheets I added data in columns J to P, that was copied from the CURR_LANE_WIDTHS worksheet to check the formula results against the expected results.
To ensure I had pasted the correct data I added a formula in column I, where 'In range' shows that the actual RRD is between the Start RRD and End RRD distances.

Unless I have made a mistake with both formulas you can see they do not match up.

It is looking like I will have to ask for one worksheet with all the values rather than do a complicated lookup.

Thanks for your help anyway.

Regards,
Dave T

Attached Files

Merged worksheets - Posted 29-11-2018.xlsx (127.3 KB, 9 views)

my apologies, it was one row out.
(Added a -1 towards the end of the formula)


Where there are errors, you can IFERROR those out.

Hello p45cal,

Just tried you addition to the formula and all is good.

I really do appreciate you time and effort.

Regards,
Dave T

Hello All,

I was showing a work colleague the formula from the Ozgrid post (in my second attachment) and he found out I had made a mistake.

The formula in columns D to G on the BARRIER_RCS worksheet should have been:
The column ID (in blue) will need to be changed each time as you drag the formula from column D to G.

Just thought I would post this followup as it is another alternative that is not an array formula.

Regards,
Dave T