SameerMirza
asked on
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
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,
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
> Please have a look at the attached spreadsheet.
There is no attachment
There is no attachment
got it !!
ASKER
sorry just uploaded
ASKER
any luck Eirman?
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)>COUNTI F(KeySrc!$ A2:$A3,$A2 ),"",INDEX (KeySrc!$B 2:$B3,SMAL L(IF(KeySr c!$A2:$A3= $A2,ROW(Ke ySrc!$A2:$ A3)-MIN(RO W(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
=IF(COLUMNS($B2:B2)>COUNTI
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
Could be done easily in vba, possible workaround using formulas attached.
test1.xls
test1.xls
ASKER
c1nmo
I was wondering if it would be possible for you to provide me the VBA workaround for unlimited records.
I was wondering if it would be possible for you to provide me the VBA workaround for unlimited records.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks alot guy.
Barry I am sure it would work for now I am happy with c1nmo's solution.
Barry I am sure it would work for now I am happy with c1nmo's solution.
ASKER
test.xls