Missing Numbers, Remove Duplicate Numbers

[MrsFrankieH]

Hello fellow Members!
My operating system is Windows 10 and use Office 10.

If you can, can you please help me with three formulas?

1. In the first problem I’m using 15 numbers from within the 1 to 41 range. On the left (A1:E3) are numbers within the 1 to 41 range (in green). The numbers in green have duplicates that I want to remove and place the remaining numbers in numerical order on the right (H1:L3).
Note: The empty cells (H3 through L3) are for times when all of the (A1:E3) are different numbers.



2. In this problem, I would like to add the missing numbers from H1:L3 to the cell on the right. The range is 1 to 41. If possible, can they be put in numerical order as well?
Note: Extra cells were added to accommodate extra numbers.





3. In this problem, can I get a formula to take the duplicate numbers from A1:E3 and put them in V1 through AA?
Note: I added extra cells just in case.



Thanks again for your help in advance. With much appreciation!!

 

[Marcelo Branco]

There is a mistake in the data sample - the number 28 in D1 is missing in H1:L3 (first problem).
If i'm right , try this:

First problem
Array formula in H1 copied across till L1 and down
=IFERROR(SMALL(IF(COUNTIF(OFFSET($A$1:$E$3,,,ROW($A$1:$E$3)-ROW($A$1)+1,COLUMN($A$1:$E$3)-COLUMN($A$1)+1),N(OFFSET($A$1,ROW($A$1:$E$3)-ROW($A$1),COLUMN($A$1:$E$3)-COLUMN($A$1))))=1,$A$1:$E$3),5*ROWS(H$1:H1)+COLUMNS($H1:H1)-5),"")
Ctrl+Shift+Enter

Second problem
Array formula in O1 copied across till S1 and down
=IFERROR(SMALL(IF(COUNTIF($H$1:$L$3,ROW($A$1:$A$41))=0,ROW($A$1:$A$41)),5*ROWS(O$1:O1)+COLUMNS($O1:O1)-5),"")
Ctrl+Shift+Enter

Third problem
Array formula in V1 copied across till Z1 and down
=IFERROR(SMALL(IF(COUNTIF(OFFSET($A$1:$E$3,,,ROW($A$1:$E$3)-ROW($A$1)+1,COLUMN($A$1:$E$3)-COLUMN($A$1)+1),N(OFFSET($A$1,ROW($A$1:$E$3)-ROW($A$1),COLUMN($A$1:$E$3)-COLUMN($A$1))))>1,$A$1:$E$3),5*ROWS(V$1:V1)+COLUMNS($V1:V1)-5),"")
Ctrl+Shift+Enter

Hope this helps

M.

 

[Marcelo Branco]

Small adjustment in the third formula

Array formula in V1 copied acroos till Z1 and down
=IFERROR(SMALL(IF(COUNTIF(OFFSET($A$1:$E$3,,,ROW($A$1:$E$3)-ROW($A$1)+1,COLUMN($A$1:$E$3)-COLUMN($A$1)+1),N(OFFSET($A$1,ROW($A$1:$E$3)-ROW($A$1),COLUMN($A$1:$E$3)-COLUMN($A$1))))=2,$A$1:$E$3),5*ROWS(V$1:V1)+COLUMNS($V1:V1)-5),"")
Ctrl+Shift+Enter

M.

 

[Marcelo Branco]

oops...
The formula has a flaw...
Re-thinking...

M.

 

[MrsFrankieH]

oops...
The formula has a flaw...
Re-thinking...

M.

I appreciate any help you can give me Marcelo!! Also, thank you for fixing my mistake. You're gem!! I appreciate your help more than I can express. I'll be back on in a couple of hours, I will let my genius Marcelo create!!!

Thanks again Marcelo!

 

[Marcelo Branco]

New version ...(correct and simpler formulas )

Array formula in H1 copied across till L1 and down
=IFERROR(SMALL(IF(FREQUENCY($A$1:$E$3,ROW($A$1:$A$41)-ROW($A$1)+1),ROW($A$1:$A$41)-ROW($A$1)+1),5*ROWS(H$1:H1)+COLUMNS($H1:H1)-5),"")
Ctrl+Shift+Enter

Array formula in O1 copied across till S1 and down
=IFERROR(SMALL(IF(COUNTIF($H$1:$L$3,ROW($A$1:$A$41)-ROW($A$1)+1)=0,ROW($A$1:$A$41)-ROW($A$1)+1),5*ROWS(O$1:O1)+COLUMNS($O1:O1)-5),"")
Ctrl+Shift+Enter

Array formula in V1 copied acroos till Z1 and down
=IFERROR(SMALL(IF(FREQUENCY($A$1:$E$3,ROW($A$1:$A$41)-ROW($A$1)+1)>1,ROW($A$1:$A$41)-ROW($A$1)+1),5*ROWS(V$1:V1)+COLUMNS($V1:V1)-5),"")
Ctrl+Shift+Enter

M.

 

[MrsFrankieH]

New version ...(correct and simpler formulas )

Array formula in H1 copied across till L1 and down
=IFERROR(SMALL(IF(FREQUENCY($A$1:$E$3,ROW($A$1:$A$41)-ROW($A$1)+1),ROW($A$1:$A$41)-ROW($A$1)+1),5*ROWS(H$1:H1)+COLUMNS($H1:H1)-5),"")
Ctrl+Shift+Enter

Array formula in O1 copied across till S1 and down
=IFERROR(SMALL(IF(COUNTIF($H$1:$L$3,ROW($A$1:$A$41)-ROW($A$1)+1)=0,ROW($A$1:$A$41)-ROW($A$1)+1),5*ROWS(O$1:O1)+COLUMNS($O1:O1)-5),"")
Ctrl+Shift+Enter

Array formula in V1 copied acroos till Z1 and down
=IFERROR(SMALL(IF(FREQUENCY($A$1:$E$3,ROW($A$1:$A$41)-ROW($A$1)+1)>1,ROW($A$1:$A$41)-ROW($A$1)+1),5*ROWS(V$1:V1)+COLUMNS($V1:V1)-5),"")
Ctrl+Shift+Enter

M.

Hello Marcelo!! Thank you so much for your time and expertise, I'm going to try it out right now. I'll keep you posted.

 

[MrsFrankieH]

Hello Marcelo!! Thank you so much for your time and expertise, I'm going to try it out right now. I'll keep you posted.

Update: Dear Marcelo, all of your formulas worked like a charm!!! I'm in complete gratitude for your help and expertise!! Thank you so much!! I will have three more problems tomorrow that need formulas for and I hope you are on and have the time. Wherever you are in the world I wish you a wonderful evening or a wonderful day. Thanks again for your help!!

 

[MrsFrankieH]

Update: Dear Marcelo, all of your formulas worked like a charm!!! I'm in complete gratitude for your help and expertise!! Thank you so much!! I will have three more problems tomorrow that need formulas for and I hope you are on and have the time. Wherever you are in the world I wish you a wonderful evening or a wonderful day. Thanks again for your help!!

Update: Hi Marcelo! After putting the formula for the last problem in, just one thing was missing. In the formula, I should have been more specific. The top row, A1, B1, C1, D1, E1 should also be omitted. Could you please either advise me or help me with a formula omitting the top row?
I hope it's not too much to ask. You've already done so much already. Thank you so much Marcelo.

 

[James006]

Hello,

First of all ... Congrats to Marcelo ... !!!

If you need to skip the row 1 from your source ... a tiny,tiny modification :

=IFERROR(SMALL(IF(FREQUENCY($A$2:$E$3,ROW($A$1:$A$41)-ROW($A$1)+1)>1,ROW($A$1:$A$41)-ROW($A$1)+1),5*ROWS(V$1:V1)+COLUMNS($V1:V1)-5),"")