Excel formula help

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

Please have al ook at the attached spreadsheet.

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

Who is Participating?
c1nmoConnect With a Mentor Commented:
lKeyRow = 2
lSourceRow = 2

With Sheets("key")

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

'\loop through source for matches

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
SameerMirzaAuthor Commented:
EirmanChief Operations ManagerCommented:
> Please have a look at the attached spreadsheet.

There is no attachment
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

EirmanChief Operations ManagerCommented:
got it !!
SameerMirzaAuthor Commented:
sorry just uploaded
SameerMirzaAuthor Commented:
any luck Eirman?
barry houdiniCommented:
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


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
Could be done easily in vba, possible workaround using formulas attached.
SameerMirzaAuthor Commented:

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

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
barry houdiniConnect With a Mentor 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


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

reagrds, barry
SameerMirzaAuthor Commented:
Thanks alot guy.
Barry I am sure it would work for now I am happy with c1nmo's solution.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.