CODEWITHSUNDEEP
excelexcel-formula
Ryan Gray
Ryan Gray

Reputation: 71

Split list into two lists depending on adjacent cell value

I need a little help, I have an Excel book with a long list of customers and in the adjacent cell is ether a 1 or 0 depending if the customer has ordered in the past month.

How can I split this list into 2 lists on another page one been ordered in the past month and the other not ordered,

Im unable to use Macros due to security levels on the PC.

Thanks

Upvotes: 0

Views: 1148

Answers (2)

jblood94
jblood94

Reputation: 17011

See the image below. Using array formulas (Ctrl+Shift+Enter) I entered this into D2:D8:

=IFERROR(INDEX(A2:A8,SMALL(IF(B2:B8,ROW(A2:A8)-ROW(A1)),ROW(A2:A8)-ROW(A1))),"")

and this into E2:E8:

=IFERROR(INDEX(A2:A8,SMALL(IF(1-B2:B8,ROW(A2:A8)-ROW(A1)),ROW(A2:A8)-ROW(A1))),"")

enter image description here

Explanation

This is how Excel will calculate the first formula:

=IFERROR(INDEX(A2:A8,SMALL(IF(B2:B8,ROW(A2:A8)-ROW(A1)),ROW(A2:A8)-ROW(A1))),"")
=IFERROR(INDEX(A2:A8,SMALL(IF({1;0;1;0;0;0;1},{2;3;4;5;6;7;8}-1),{2;3;4;5;6;7;8}-1)),"")
=IFERROR(INDEX(A2:A8,SMALL(IF({1;0;1;0;0;0;1},{1;2;3;4;5;6;7}),{1;2;3;4;5;6;7})),"")
=IFERROR(INDEX(A2:A8,SMALL({1;FALSE;3;FALSE;FALSE;FALSE;7},{1;2;3;4;5;6;7})),"")

Since the second argument of SMALL is an array, it will find the 1st, 2nd, 3rd, etc. value, ignoring FALSE (it will return #NUM! for the 4th through 7th value).

=IFERROR(INDEX(A2:A8,{1;3;7;#NUM!;#NUM!;#NUM!;#NUM!}),"")
=IFERROR({"A";"C";"G";#NUM!;#NUM!;#NUM!;#NUM!},"")
={"A";"C";"G";"";"";"";""}

Upvotes: 1

user4039065
user4039065

Reputation:

Try,

'for ones
=INDEX(A:A, AGGREGATE(15, 7, ROW(B$2:INDEX(B:B, MATCH(1E+99, B:B)))/(B$2:INDEX(B:B, MATCH(1E+99, B:B))=1), ROW(1:1)))
'for zeroes
=INDEX(A:A, AGGREGATE(15, 7, ROW(B$2:INDEX(B:B, MATCH(1E+99, B:B)))/(B$2:INDEX(B:B, MATCH(1E+99, B:B))=0), ROW(1:1)))

Of course, you will have to modify to suit the different worksheet.

enter image description here

Upvotes: 2

Related Questions