Autofill with exclusions

[StephenSLR]

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

 

[shaowu459]

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

 

[StephenSLR]

=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

 

[shaowu459]

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.

 

[StephenSLR]

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]

Enter the formula in any cell is OK.

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

 

[StephenSLR]

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

 

[shaowu459]

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.

=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.

 

[StephenSLR]

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.

=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

 

[shaowu459]

[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.