Excel concatenation by using formula or vlookup

Question

I have two sheets in excel workbook. I need the formula which matches SITEID and concatenate 3 values to 1 separated by / . I have tried to use Vlookup with ranges but not get the actual results.

First sheet contains (4 columns):

SiteId  Cell    Sector  Bore
MDLH1238    12381   1   10
MDLH1238    12382   2   20
MDLH1238    12383   3   40
MDLH1239    12391   1   60
MDLH1240    12401   1   50
MDLH1240    12402   2   20

Second sheet contains (2 columns):

SiteId  Bore
MDLH1238
MDLH1239
MDLH1240    

The expected result should be like this :

SiteId  Bore
MDLH1238 10/20/30
MDLH1239 60 
MDLH1240 50/20

Answer 1

Assuming there's only ever 3 sectors per site, and using your sheet names, and assuming your cells start in A1, enter these in Sheet2, adjacent to the SiteIDs:

Cell B2:

=SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A2,Sheet1!$C$2:$C$7,1)&"/"&SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A2,Sheet1!$C$2:$C$7,2)&"/"&SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A2,Sheet1!$C$2:$C$7,3)

Cell B3:

=SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A3,Sheet1!$C$2:$C$7,1)&"/"&SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A3,Sheet1!$C$2:$C$7,2)&"/"&SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A3,Sheet1!$C$2:$C$7,3)

Cell B4:

=SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A4,Sheet1!$C$2:$C$7,1)&"/"&SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A4,Sheet1!$C$2:$C$7,2)&"/"&SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A4,Sheet1!$C$2:$C$7,3)

That should produce content like:

SiteId    Bore
MDLH1238  10/20/40
MDLH1239  60/0/0
MDLH1240  50/20/0

That's got extra "/0" entries where the sector 2/3 don't exist, but the formulas get longer and harder to read, to do that.

Edit: Adds the formulas for dealing with "/0"

Cell B2:

=SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A2,Sheet1!$C$2:$C$7,1)&IF(SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A2,Sheet1!$C$2:$C$7,2)>0,"/"&SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A2,Sheet1!$C$2:$C$7,2),"")&IF(SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A2,Sheet1!$C$2:$C$7,3)>0,"/"&SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A2,Sheet1!$C$2:$C$7,3),"")

Cell B3:

=SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A3,Sheet1!$C$2:$C$7,1)&IF(SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A3,Sheet1!$C$2:$C$7,2)>0,"/"&SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A3,Sheet1!$C$2:$C$7,2),"")&IF(SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A3,Sheet1!$C$2:$C$7,3)>0,"/"&SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A3,Sheet1!$C$2:$C$7,3),"")

Cell B4:

=SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A4,Sheet1!$C$2:$C$7,1)&IF(SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A4,Sheet1!$C$2:$C$7,2)>0,"/"&SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A4,Sheet1!$C$2:$C$7,2),"")&IF(SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A4,Sheet1!$C$2:$C$7,3)>0,"/"&SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A4,Sheet1!$C$2:$C$7,3),"")

Output:

SiteId    Bore
MDLH1238  10/20/40
MDLH1239  60
MDLH1240  50/20

Answer 2

What you would like to be able to do is to concatenate elements of an array using "/" as a separator. Unfortunately native Excel can't do this, but there are plenty of good VBA functions already written which will, e.g. the one described here.

So I would suggest this solution:-

=StringConcat("/",IF(Sheet1!A$2:A$7=A2,Sheet1!D$2:D$7,""))

starting in cell B2 of the second sheet and pulled down as necessary. This is an array formula and must be entered using CtrlShiftEnter.

In order to use the StringConcat function, you need to copy the code from the web page, press AltF11, select Insert|Module, then paste in the code.