Autofill with exclusions

StephenSLR

New Member
Joined
May 24, 2018
Messages
23
Currently in Aus. a lot of new Chinese immigrants are buying units so developers are kowtowing to their tetraphobia by building unit blocks without a fourth floor or floors without the number four same goes with the unit numbers.

See link:

http://www.dailymail.co.uk/news/article-3481168/New-Sydney-apartment-building-deliberately-left-floors-number-four-sounds-similar-death-Chinese.html

I have to create spreasheets without unit numbers that have the number 4 in them, below is an example of the ground floor units:

G.01
G.02
G.03
G.05
G.06
G.07
G.08
G.09
G.10
G.11
G.12
G.13
G.15

etc.

Is there a way to autofill and exclude the number 4 and skip to 5 or would it be easier to get the Aus. govt. to ban Chinese immigration? lol.

s
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

shaowu459

Well-known Member
Joined
Apr 26, 2018
Messages
504
Office Version
  1. 365
Platform
  1. Windows
Ha, please try following formula:

=TEXT(SMALL(IF(ISNUMBER(FIND(4,ROW($1:$99))),"@",ROW($1:$99)),ROW(A1)),"\G\.00")

Enter the formula in any cell and Ctrl+Shift+Enter finish. The formula removes all nubmers contains 4, for example, 4,14,41,44, etc.

I'm chinese, I understand this very much:LOL:
 
Last edited:

StephenSLR

New Member
Joined
May 24, 2018
Messages
23
=TEXT(SMALL(IF(ISNUMBER(FIND(4,ROW($1:$99))),"@",ROW($1:$99)),ROW(A1)),"\G\.00")

Enter the formula in any cell and Ctrl+Shift+Enter finish.

Thanks for the reply and your good sense of humour but I'm not understanding.

I put the formula in a cell and after I hit enter I see G.01 in that cell.

What do I do next?

Dragging the auto handle does not work, I get #NUM ! in every cell and holding Ctrl+Shift+Enter does not allow me to drag the handle.

s
 
Last edited:

shaowu459

Well-known Member
Joined
Apr 26, 2018
Messages
504
Office Version
  1. 365
Platform
  1. Windows
1)Copy formula;
2)double click cell A1;
3)past formula in cell A1;
4)do not exit edit mode by hit Enter, hold Ctrl and Shift, then press Enter to finish, not just press Enter.
5)then copy formula down or use the autofill handle fill down.
 
Last edited:

StephenSLR

New Member
Joined
May 24, 2018
Messages
23

ADVERTISEMENT

1)Copy formula;
2)double click cell A1;
3)past formula in cell A1;
4)do not exit edit mode by hit Enter, hold Ctrl and Shift, then press Enter to finish, not just press Enter.
5)then copy formula down or use the autofill handle fill down.

Thanks, that worked great.

I however don't always start in cell A1 and as you would know if you've lived in apartments, is that each floor has only so many units and the next floor units will have different unit numbers.

i.e.

Ground floor
G.01
G.02
G.03
G.05
G.06
G.07

First Floor
1.01
1.02
1.03
1.05
1.06
1.07

and so on. Of course there is no fourth floor.

lol.

What do I do in this case?

s
 

shaowu459

Well-known Member
Joined
Apr 26, 2018
Messages
504
Office Version
  1. 365
Platform
  1. Windows
Enter the formula in any cell is OK.

Do you have unit number like 1.41, 1.42, 1.43, 2.43, 2.46?
 
Last edited:

StephenSLR

New Member
Joined
May 24, 2018
Messages
23

ADVERTISEMENT

Do you have unit number like 1.41, 1.42, 1.43, 2.43, 2.46?

No, they are too superstitious to purchase any unit with 4 in any configuration.

I placed the formula in C10 and changed the A1 to C10 and the G to a 1 but it started from 1.11 in that cell, lol.

1.11
1.12
1.13
1.15

I worked it out; all was good when I changed C10 to C1.

Thanks for this, it is a great help.

s
 
Last edited:

shaowu459

Well-known Member
Joined
Apr 26, 2018
Messages
504
Office Version
  1. 365
Platform
  1. Windows
A more easier and practical way to do this is:

1)Enter formula in any cell and copy down, for say copy to A1 to A 800.
Code:
=TEXT(ROW(A101)/100,"0.00")
2)copy A1:A800 and paste values.
3)Use autofilter function, criteria may be contains 4 or end with 4.
4)select all cells filtered after step 3. Ctrl+G, Special, visible cells only, Click OK to close the dialogue box.
5)right click your mouse, and select Delete, and the select Shift cell up.
6)after the 5 steps, you will get a continuous list of unit numbers without 4 or ending with 4.
 
Last edited:

StephenSLR

New Member
Joined
May 24, 2018
Messages
23
A more easier and practical way to do this is:

1)Enter formula in any cell and copy down, for say copy to A1 to A 800.
Code:
=TEXT(ROW(A101)/100,"0.00")
2)copy A1:A800 and paste values.
3)Use autofilter function, criteria may be contains 4 or end with 4.
4)select all cells filtered after step 3. Ctrl+G, Special, visible cells only, Click OK to close the dialogue box.
5)right click your mouse, and select Delete

I got this far but the visible cells also include the first cell 1.01 after step 3.

Then when I highlight the selection and right click, there is only 'delete row' in the options and it goes weird when I do that.

Also, where is the Shift Cell Up function?

s
 
Last edited:

shaowu459

Well-known Member
Joined
Apr 26, 2018
Messages
504
Office Version
  1. 365
Platform
  1. Windows
[I got this far but the visible cells also include the first cell 1.01 after step 3]
Because excel recognise the first cell as header of the list, you can add a header mannually, or just select range below the first cell.

[
there is only 'delete row' in the options and it goes weird when I do that.]
Yes, select delete row is OK, and then select delete entire row. I list the steps by memory, it is not so accurate, sorry for that.

Please be sure you did step 2:copy A1:A800 and paste values.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,271
Messages
5,527,708
Members
409,784
Latest member
lalz1205

This Week's Hot Topics

Top