# Excel formula help

on
Hi,
How do you I say while logic in excel formula.
OR maybe ue array formula to achieve it.

On tab key I have a key and ref.
in Ref column I am using  -> IF(ISNA(VLOOKUP(A2,rngRef,2,FALSE)),"",VLOOKUP(A2,rngRef,2,FALSE))
To get ref_src from RANGE -> rngRef on tab KeySrc
basically just mapping the keys and getting the corresponding ref

Please let me know irf its not clear. I am sure you will figure out quikcly by looking at the attached sheet

Now What I am trying to acheive is that
there are two rows/records with same key '123'
and I am trying to get Ref  and combination of Ref_src for the same key
in the from Ref_src, Ref_src
so for key = 123
Ref would look like a,b

It would be very helpful if some one could help me out in achieving this

thanks
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Chief Operations Manager

Commented:

There is no attachment
Chief Operations Manager

Commented:
got it !!

Commented:

Commented:
any luck Eirman?
Most Valuable Expert 2013

Commented:
It's dificult to get the results in one cell with a formula (especially for more data which I asime you have) so how about the attached to get each Ref in a separate cell, i.e. in B2 paste this formula

=IF(COLUMNS(\$B2:B2)>COUNTIF(KeySrc!\$A2:\$A3,\$A2),"",INDEX(KeySrc!\$B2:\$B3,SMALL(IF(KeySrc!\$A2:\$A3=\$A2,ROW(KeySrc!\$A2:\$A3)-MIN(ROW(KeySrc!\$A2:\$A3))+1),COLUMNS(\$B2:B2))))

confirmed with CTRL+SHIFT+ENTER nad copied across - when matches are exhausted you get blanks, e.g. in your example the Ref 3 formula in D2 displays a blank, see attached

regards, barry
27288594.xls

Commented:
Could be done easily in vba, possible workaround using formulas attached.
test1.xls

Commented:
c1nmo

I was wondering if it would be possible for you to provide me the VBA workaround for unlimited records.

Commented:
Barry,

it seems to work find for the existing records.
I was wondering if it owuld be possible for you write the one that would work for
multiple records.
may be using a range for the source
Commented:
'\init
lKeyRow = 2
lSourceRow = 2

With Sheets("key")

'\loop through keys
Do
sKey = .Cells(lKeyRow, 1)

'\loop through source for matches

Do
sSource = Sheets("keysrc").Cells(lSourceRow, 1)
If sKey = sSource Then
sConcat = sConcat & Sheets("keysrc").Cells(lSourceRow, 2)
End If

lSourceRow = lSourceRow + 1
Loop While sSource <> ""

'\update key
.Cells(lKeyRow, 2) = sConcat

'\reset source vars
sConcat = ""
lSourceRow = 2

'\inc keyrow
lKeyRow = lKeyRow + 1

Loop While .Cells(lKeyRow, 1) <> ""

End With
Most Valuable Expert 2013
Commented:
>I was wondering if it owuld be possible for you write the one that would work for
multiple records

Hello SameerMirza, the formula I suggested is easily extendable, e.g. changing all the A3 references to A50 we get this version

=IF(COLUMNS(\$B2:B2)>COUNTIF(KeySrc!\$A2:\$A50,\$A2),"",INDEX(KeySrc!\$B2:\$B50,SMALL(IF(KeySrc!\$A2:\$A50=\$A2,ROW(KeySrc!\$A2:\$A50)-MIN(ROW(KeySrc!\$A2:\$A50))+1),COLUMNS(\$B2:B2))))

copied across as before and down if necessary, see attached revised version

reagrds, barry
27288594v2.xls

Commented:
Thanks alot guy.
Barry I am sure it would work for now I am happy with c1nmo's solution.

Do more with