Formula to combine 2 results

**ssdsibes** · 11-16-2010, 08:26 PM

I have a spreadsheet with 2 worksheets. First worksheet is "data" and the second sheet is "zone".

The Main sheet has listed unique property id's represented by a number. There is only ever 1 property id number listed.

On the 2nd sheet, "zone" I have the same property number listed in column A, but the property number may appear 2 or three times. I need to obtain the value from worksheet "zone" in column C and move each value for the property number it to Column AH in the worksheet "data" but making it so that it lists the value by a semi colan.

**martindwilson** · 11-16-2010, 08:36 PM

prob quite easy but a sample work book would be handy !

**ssdsibes** · 11-16-2010, 09:15 PM

See attached file.
thanks
Ang

**Monpasdg** · 11-16-2010, 11:52 PM

I attached my interpretation of what you needed. I used a Index/Match to find the value from the Data Sheet on the Zone sheet.

You are going to have issues with this data however. I noticed you dont have any unique data on your Zone sheet. With out a unique key, finding the right column "C" field is going to be impossible. The current formula just finds the first value that matches:

Basic "AH" Column Formula:

=INDEX(Zone!A:C,MATCH(A2,Zone!A:A,0),3)

You said you wanted a semi-colon to separate data. This formula just concatenates Column A with the result of the formula above, separated with a semi colon. The & is your concatenation

=A2 & ";" & INDEX(Zone!A:C,MATCH(A2,Zone!A:A,0),3)

Lastly, your data is sometimes not on your Zone sheet, i put in an Iferror to say "not found" if the PCL number doesn't exist

=IFERROR(A2 & ";" & INDEX(Zone!A:C,MATCH(A2,Zone!A:A,0),3),"Not Found")

Cheers,

Don

**ssdsibes** · 11-17-2010, 12:05 AM

Thank you Don
My issue is that the data on the Zone sheet has duplicate parcel numbers. So i need a smart way to find where the parcel number in the Data sheet matches both the parcel numbers in the Zone sheet, then return the two values into one cell, separated by a semi colan.

Is this possible or am I asking for too much?

Ang

**martindwilson** · 11-17-2010, 08:22 AM

well i manage to do it with functions but it took ages to calculate due to the number of index/matches going on. then i forgot to save the finished thing! probably a vba solution is the way to go
ok i had another go this works better
making 2 assumptions
1 the pcl_num on sheet zone! is always consecutive in singles pairs or triplets
2. you'll never have more than 3 identical pcl_num's