Need help with a complex formula I cant solve. Please help. Re-order column numbers.....

**lagiosman** · 03-31-2021, 10:33 AM

Hello, need help with a formula

Ok heres the sample and I will explain what is the problem above:

	A	B	C
1	Priority	Re-Ordered Priority	Team Or Single
2	3		Team 1
3	10		Team 1
4	9		Team 2
5	1		Team 2
6	6		Team 3
7	2		Team 3
8	5		Team 4
9	8		Team 4
10	7		Team 4
11	4		Team 4

I have write a complex formula to give priorities in the column A and a second complex formula to check somes Dates in another column (i have not inlcude it here) and if a date of a row it's the same with another row, then eg on C2 cell we have a Team and not a Single task, the same formula are able to separate Teams, as you can see Team 1, Team 2, etc...

What I have not write and I have no inspiration at all and I don't know what to do is: HOW TO RE-ORDER PRIORITIES IN COLUMN B IF WE HAVE A TEAM IN COLUMN C AND NOT A SINGLE

Let me give an example:

	A	B	C
1	Priority	Re-Ordered Priority	Team Or Single
2	3	1	Team 1
3	10	2	Team 1
4	9	2	Team 2
5	1	1	Team 2
6	6	2	Team 3
7	2	1	Team 3
8	5	1	Team 4
9	8	4	Team 4
10	7	3	Team 4
11	4	2	Team 4

As you can see, what I want is for example B2 takes the value 1 because A2 is smaller than A3 and of cource because we have a Team not a Single on C2.
This is an easy formula because we have only 2 cells to check that belongs to Team 1
The formula goes like this:

=IF(C2<>"Single",IF(A2<A3;1;2),A2)

Ok now let's go for the second row.
I guess the formula goes like this to check both next and previous cells:

=IF(C2<>"Single",IF(AND(C3=C2,C3=C4),IF(MIN(A2:A4)=A3,1,IF(MAX(A2:A4,3,2))),IF(C3=C2,IF(A3<A2,1,2),IF(C3=C4,IF(A3<A4,1,2)))),A3)

Now the formula is still problematic, because if we have a team with more than 2 rows, eg TEAM 4 have 4 rows, I will need to write some kind of bigger formula or to write a formula with a combination of INDEX or Vlookup???

I really don't know, for some reason I have stuck.

I need help. Please. Any ideas???

Thanks, Nicolas

------------------------------UPDATE
This is the solution, thanks to Fluff13