Autofill with exclusions

StephenSLR

New Member
Joined
May 24, 2018
Messages
28
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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:
Upvote 0
=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:
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
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:
Upvote 0
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:
Upvote 0
[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:
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top