Excel formula help

SameerMirza
SameerMirza used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
EirmanChief Operations Manager

Commented:
> Please have a look at the attached spreadsheet.

There is no attachment
EirmanChief Operations Manager

Commented:
got it !!
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Author

Commented:
sorry just uploaded

Author

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

Author

Commented:
c1nmo

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

Author

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

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial