Excel concatenation by using formula or vlookup

0
votes

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
2
excelvbaexcel-formulavlookup
Match, then index the values off that, so match gets the row based on the ID, then index the range (col x,this row result). Match and Index. – Nathan_Sav
I don't think INDEX will be reliable, given that not all sites have 3 entries... You'll end up indexing into a different site? – ThunderFrame

2 Answers

0
votes

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
0
votes

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.

enter image description here