Excel formula help

Hi,
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

thanks
SameerMirzaAsked:
Who is Participating?
 
c1nmoConnect With a Mentor 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
0
 
SameerMirzaAuthor Commented:
0
 
EirmanChief Operations ManagerCommented:
> Please have a look at the attached spreadsheet.

There is no attachment
0
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 !!
0
 
SameerMirzaAuthor Commented:
sorry just uploaded
0
 
SameerMirzaAuthor Commented:
any luck Eirman?
0
 
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

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

I was wondering if it would be possible for you to provide me the VBA workaround for unlimited records.
0
 
SameerMirzaAuthor 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
0
 
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

=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
0
 
SameerMirzaAuthor Commented:
Thanks alot guy.
Barry I am sure it would work for now I am happy with c1nmo's solution.
0
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.