Looking for formula (non-array preferred and ignoring blanks) to look into sheet named Okay, sort column A alphabetically and extract values in this sheet (column B of sheet named Okay) into column A to column I of sheet named Here. Desired outcome is in sheet named Here in column A to column I.
See attached sample file.
Formula should ignore blanks (non-array preferred).
Thanks.
Hi,
in A2 to be copied down
=INDEX(Okay!A$2:A$30,MATCH(AGGREGATE(15,6,COUNTIF(Okay!A$2:A$30,"<"&Okay!A$2:A$30)+1,ROWS($1:1)),INDEX(COUNTIF(Okay!A$2:A$30,"<"&Okay!A$2:A$30)+1,),0))
in B2 to copy down and to the right
=INDEX(Okay!B$2:B$30,AGGREGATE(15,6,ROW($2:$30)-1/(Okay!$A$2:$A$30=$A2),COUNTIF($A$2:$A2,$A2)))
to hide not welcome zeros, in F2 a quick fix
=INDEX(Okay!F$2:F$30&"",AGGREGATE(15,6,ROW($2:$30)-1/(Okay!$A$2:$A$30=$A2),COUNTIF($A$2:$A2,$A2)))
Regards
Last edited by canapone; 02-01-2021 at 02:44 AM.
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.
Please, mark your thread [SOLVED] if you received your answer.
bjnockle, please take a few minutes to close out the other 3-4 similar-ish threads that you have started and which are still hanging in mid-air... waiting for comment & closure.
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
Glenn Kennedy: They are closed now. Thanks for the reminder to close these threads.
canapone: Your proposed formula (non-array preferred) is not pulling correctly when I extended the range fromNote: i added data in A33 and A34 with blanks/empty cells in A31 and A32. The formula should ignore empty cells/blanks.
Please Login or Register to view this content.
Please take a look and help fix. See attached sample file. Thanks.
Copy the data to another sheet and sort the data On ColumnA. No formula is required.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
kvsrinivasamurthy: looking for formula (non-array preferably) as the data will be changing and the sheet will be hidden to end-users. Thanks.
Please try at A2
=IFERROR(INDEX(Okay!$A$1:$A$40,MOD(AGGREGATE(15,6,COUNTIFS(Okay!$A$2:$A$40,"<"&Okay!$A$2:$A$40)*10^6+ROW(Okay!$A$2:$A$40)/(Okay!$A$2:$A$40>0),ROWS(A$2:A2)),10^6)),"")
Bo_Ry: Your proposed solution pulled the values, but it did not start from A2 in sheet named Here_1. Please help take a look and fix. See attached sample file. Thanks.
A2:
=IFERROR(LOOKUP(2,1/(COUNTIF(Okay!$A$2:$A$30,"<="&Okay!$A$2:$A$30)=AGGREGATE(15,6,COUNTIF(Okay!$A$2:$A$30,"<="&Okay!$A$2:$A$30),ROW(Okay!1:1))),Okay!$A$2:$A$30),"")
See attachment
B2:
=IFERROR(INDEX(Okay!B$1:B$30&"",AGGREGATE(15,6,ROW(Okay!$A$2:$A$30)/(Okay!$A$2:$A$30=$A2),ROWS(INDIRECT("1:"&COUNTIF($A$2:$A2,$A2))))),"")
Drag accross to I2
In C2 & D2: At the end of formula, +0 to get real time (number)
INDIRECT("1:"&COUNTIF($A$2:$A2,$A2)))))+0,"")
Drag all down
Quang PT
Change in Red
Formula:
Please Login or Register to view this content.
Without the volatile INDIRECT function.
in A2:
=INDEX(Okay!$A$2:$A$50,MATCH(LARGE(INDEX((COUNTIF(Okay!$A$2:$A$50,">="&Okay!$A$2:$A$50)),0),ROWS(A$2:A2)),INDEX((COUNTIF(Okay!$A$2:$A$50,">="&Okay!$A$2:$A$50)),0),0))&""
copied down.
In B2, copied across and down:
=IF($A2="","",INDEX(Okay!B:B,AGGREGATE(15,6,ROW(Okay!$A$2:$A$50)/(Okay!$A$2:$A$50=$A2),COUNTIF($A$2:$A2,$A2))))&""
Modify in columns returning NUMBERS to:
=IFERROR(IF($A2="","",1/(1/INDEX(Okay!C:C,AGGREGATE(15,6,ROW(Okay!$A$2:$A$50)/(Okay!$A$2:$A$50=$A2),COUNTIF($A$2:$A2,$A2))))),"")
format columns C&D as time. General for the rest.
Glenn Kennedy: Outstanding solution. Thanks
Bo_Ry: Outstanding solution. Thanks a lot.
bebo021999: Great solution. Thanks a lot.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks