LinkBack URL
About LinkBacks
How to arrange the data as enclosed in my attachment.
Unique value to be kept in column A.All other data is self explanatory.
How to arrange the data as enclosed in my attachment.
Unique value to be kept in column A.All other data is self explanatory.
PowerQuery can do it.
M Code:
Excel 2016 (Windows) 32 bit
Please Login or Register to view this content.
Particulars a b c d Kapil Arnold Schewe Arnold Arnold Sch RAM USA 2 USA,2 Tony Blair Tony UK UK Ltd UK,Ltd UK,West
Sheet: Expected Result
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
With a formula
In D2 copied down & across
=IFERROR(INDEX(question!$B$3:$B$15,MATCH($A2&"|"&D$1,question!$A$3:$A$15&"|"&question!$C$3:$C$15,0)),"")
Needs to be confirmed with Ctrl Shift Enter, rather than Enter.
What about A column of unique values ?.I have a similar large data say more than 1400 rows.I think array formula in large data set get slower.I hope faster for the same.
Last edited by AliGW; 06-27-2020 at 04:26 AM. Reason: Please don't quote unnecessarily!
Why not use my suggestion of PowerQuery?
This is so becoz I have to share the file in version 2007 and excel web where powe query doesn't work.
OK - next time I offer a solution, please don't just ignore it: common courtesy requires you to acknowledge ALL help offered. If your requirements will be limited by an old version, please say so in your opening post so that people do not end up wasting their time. Thanks.
This will get the unique values for col A
In A2 copied down
=IFERROR(INDEX(question!$A$3:$A$15,MATCH(0,COUNTIFS(A$1:A1,question!$A$3:$A$15),0)),"")
Needs Ctrl Shift Enter
If u don't mind,can you help without CSE also w.r.t post #3 & #8.I have felt in large data set CSE takes more time than other.
You will need array formulae to do what you are asking for.
No,I mean to say without array formula,how to get the same work done i.e non array formula where ctrl+shift+enter is not used.
You can't - read post #10 again.
I have read the post#10.I was requesting that without array formula,can normal formula be there instead.
Fluff said this:
This means that the answer to your question is no, you cannot do what you want without using an array formula.You will need array formulae to do what you are asking for.
Ok.I got the work done with array formula.But further I was expecting that if alternative formula is there instead of array formula it would help me in some other works which is related to those sample.data mentioned.
If exists plz let me know.
Thanks for all the support and help to both of you.
Glad we could help & thanks for the feedback.
It doesn'tIf exists plz let me know.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks