LinkBack URL
About LinkBacks
Looking for non-array formula to extract values in A2:D26 and ignore blanks. Extracted values to go into G2:J21 without blanks. See sample file.
Thanks.
Looking for non-array formula to extract values in A2:D26 and ignore blanks. Extracted values to go into G2:J21 without blanks. See sample file.
Thanks.
In G2, copied across & down\;
=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$26)/($A$2:$A$26<>""),ROWS(G$2:G2))),"")
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
While that works in your sample, it may not ALWAYS work, if the blanks are in different rows. To be safe, use this instead:
=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$26)/(A$2:A$26<>""),ROWS(G$2:G2))),"")
Glenn Kennedy: I am getting in accurate pull when I adjusted the formula range from A:A to A$2:A$26. Please help fix. See adjusted formula.
Thanks
Please Login or Register to view this content.
Change it back. It was intended to be A:A.
Glenn Kennedy: but if it were to start from A2:A26, can you help adjust the formula?
Thanks.
There is NO advantage in setting an exact range. INDEX is perfectly efficient using a whole column reference. However, if that's what you really want....
=IFERROR(INDEX(A$2:A$26,AGGREGATE(15,6,ROW(A$2:A$26)-ROW(A$2)+1/(A$2:A$26<>""),ROWS(G$2:G2))),"")
Glenn: works like a charm. Thanks for the solution.
No problem.
Glenn: noticed that formula is not pulling accurate values when there is a value in range A2:A27 and there is no corresponding value in B2:B27. See A15 (Pearlie) and B15 (blank) and A24 (June) and B24 (blank). Please help fix formula to account for this. See inaccurate pull in red color (G15:H21). See attached file.
Apols. Read post 3 and adapt it. I'm away from the PC.
Glenn Kennedy: adapted and still not working.
This works with your sample:
=IFERROR(INDEX(A$2:A$27,AGGREGATE(15,6,ROW(A$2:A$27)-ROW(A$2)+1/($A$2:$A$27<>""),ROWS(G$2:G2)))&"","")
However, if col A is blank and there is an entry in B, C, or D - it still will not work. can that happen in your real data?
Glenn Kennedy:works! Great solution Glenn. Thanks my friend.Please Login or Register to view this content.
Twice in one thread... you're welcome. No problem....
Glenn: col A (A2:A27) will never be completely blank. There are going to be values in column A. However, a few cells might contain blank cells and corresponding values. This formula addressed that scenario.Thanks for the great solution.Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks