Match columns, sort and add gaps in row/s if required

working off the assumption that watermelon < Strawberry is a typo...

	A	B	C	D	E	F	G	H	I	J	K
1	Input
2	List A	List B		A Only	B Only	A Both	B Both	A More A	B More B	FirstRow A	FirstRow B
3	Apple	Banana		TRUE	FALSE	FALSE	TRUE	FALSE	FALSE	0	1
4	Banana	custard apple		FALSE	TRUE	TRUE	FALSE	TRUE	FALSE	1	3
5	Banana	Mango		FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	1	4
6	Mango	Mango		FALSE	FALSE	TRUE	TRUE	FALSE	TRUE	4	4
7	watermelon	Strawberry		TRUE	FALSE	FALSE	TRUE	FALSE	TRUE	7	6
8	Strawberry			FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	6	0

Cell Formulas

	A	B	C	D	E	F	G	H	I	J	K
1	Input
2	List A	List B		A Only	B Only	A Both	B Both	A More A	B More B	FirstRow A	FirstRow B
3	Apple	Banana		=AND(NOT(ISBLANK(A3)),COUNTIF($B$3:$B$8,A3)=0)	=AND(NOT(ISBLANK(B3)),COUNTIF($A$3:$A$8,B3)=0)	=AND(NOT(D3),NOT(ISBLANK(A3)))	=AND(NOT(E3),NOT(ISBLANK(B3)))	=AND(F3,COUNTIF($A$3:$A$8,A3)>COUNTIF($B$3:$B$8,A3))	=AND(G3,COUNTIF($A$3:$A$8,B3)<=COUNTIF($B$3:$B$8,B3))	=SUMPRODUCT(($D$3:$D$8+$H$3:$H$8)*($A$3:$A$8<A3)+($E$3:$E$8+$I$3:$I$8)*($B$3:$B$8<A3))	=SUMPRODUCT(($D$3:$D$8+$H$3:$H$8)*($A$3:$A$8<B3)+($E$3:$E$8+$I$3:$I$8)*($B$3:$B$8<B3))
4	Banana	custard apple		=AND(NOT(ISBLANK(A4)),COUNTIF($B$3:$B$8,A4)=0)	=AND(NOT(ISBLANK(B4)),COUNTIF($A$3:$A$8,B4)=0)	=AND(NOT(D4),NOT(ISBLANK(A4)))	=AND(NOT(E4),NOT(ISBLANK(B4)))	=AND(F4,COUNTIF($A$3:$A$8,A4)>COUNTIF($B$3:$B$8,A4))	=AND(G4,COUNTIF($A$3:$A$8,B4)<=COUNTIF($B$3:$B$8,B4))	=SUMPRODUCT(($D$3:$D$8+$H$3:$H$8)*($A$3:$A$8<A4)+($E$3:$E$8+$I$3:$I$8)*($B$3:$B$8<A4))	=SUMPRODUCT(($D$3:$D$8+$H$3:$H$8)*($A$3:$A$8<B4)+($E$3:$E$8+$I$3:$I$8)*($B$3:$B$8<B4))
5	Banana	Mango		=AND(NOT(ISBLANK(A5)),COUNTIF($B$3:$B$8,A5)=0)	=AND(NOT(ISBLANK(B5)),COUNTIF($A$3:$A$8,B5)=0)	=AND(NOT(D5),NOT(ISBLANK(A5)))	=AND(NOT(E5),NOT(ISBLANK(B5)))	=AND(F5,COUNTIF($A$3:$A$8,A5)>COUNTIF($B$3:$B$8,A5))	=AND(G5,COUNTIF($A$3:$A$8,B5)<=COUNTIF($B$3:$B$8,B5))	=SUMPRODUCT(($D$3:$D$8+$H$3:$H$8)*($A$3:$A$8<A5)+($E$3:$E$8+$I$3:$I$8)*($B$3:$B$8<A5))	=SUMPRODUCT(($D$3:$D$8+$H$3:$H$8)*($A$3:$A$8<B5)+($E$3:$E$8+$I$3:$I$8)*($B$3:$B$8<B5))
6	Mango	Mango		=AND(NOT(ISBLANK(A6)),COUNTIF($B$3:$B$8,A6)=0)	=AND(NOT(ISBLANK(B6)),COUNTIF($A$3:$A$8,B6)=0)	=AND(NOT(D6),NOT(ISBLANK(A6)))	=AND(NOT(E6),NOT(ISBLANK(B6)))	=AND(F6,COUNTIF($A$3:$A$8,A6)>COUNTIF($B$3:$B$8,A6))	=AND(G6,COUNTIF($A$3:$A$8,B6)<=COUNTIF($B$3:$B$8,B6))	=SUMPRODUCT(($D$3:$D$8+$H$3:$H$8)*($A$3:$A$8<A6)+($E$3:$E$8+$I$3:$I$8)*($B$3:$B$8<A6))	=SUMPRODUCT(($D$3:$D$8+$H$3:$H$8)*($A$3:$A$8<B6)+($E$3:$E$8+$I$3:$I$8)*($B$3:$B$8<B6))
7	watermelon	Strawberry		=AND(NOT(ISBLANK(A7)),COUNTIF($B$3:$B$8,A7)=0)	=AND(NOT(ISBLANK(B7)),COUNTIF($A$3:$A$8,B7)=0)	=AND(NOT(D7),NOT(ISBLANK(A7)))	=AND(NOT(E7),NOT(ISBLANK(B7)))	=AND(F7,COUNTIF($A$3:$A$8,A7)>COUNTIF($B$3:$B$8,A7))	=AND(G7,COUNTIF($A$3:$A$8,B7)<=COUNTIF($B$3:$B$8,B7))	=SUMPRODUCT(($D$3:$D$8+$H$3:$H$8)*($A$3:$A$8<A7)+($E$3:$E$8+$I$3:$I$8)*($B$3:$B$8<A7))	=SUMPRODUCT(($D$3:$D$8+$H$3:$H$8)*($A$3:$A$8<B7)+($E$3:$E$8+$I$3:$I$8)*($B$3:$B$8<B7))
8	Strawberry			=AND(NOT(ISBLANK(A8)),COUNTIF($B$3:$B$8,A8)=0)	=AND(NOT(ISBLANK(B8)),COUNTIF($A$3:$A$8,B8)=0)	=AND(NOT(D8),NOT(ISBLANK(A8)))	=AND(NOT(E8),NOT(ISBLANK(B8)))	=AND(F8,COUNTIF($A$3:$A$8,A8)>COUNTIF($B$3:$B$8,A8))	=AND(G8,COUNTIF($A$3:$A$8,B8)<=COUNTIF($B$3:$B$8,B8))	=SUMPRODUCT(($D$3:$D$8+$H$3:$H$8)*($A$3:$A$8<A8)+($E$3:$E$8+$I$3:$I$8)*($B$3:$B$8<A8))	=SUMPRODUCT(($D$3:$D$8+$H$3:$H$8)*($A$3:$A$8<B8)+($E$3:$E$8+$I$3:$I$8)*($B$3:$B$8<B8))

of course this would be a whole lot easier with VBA

Please Login or Register  to view this content.