Formula to Populate Text In a Column

[kumara_faith]

Hi,

I have the following table:

Excel Workbook
	A	B
3	Document Type	Document Number
4	Contract	12545876
5	*	12545877
6	*	12545878
7	*	12545879
8	*	12545880
9	*	12545881
10	*	12545882
11	*	12545883
12	Title	12545884
13	*	12545885
14	*	12545886
15	*	12545887
16	*	12545888
17	*	12545889
18	*	12545890
19	*	12545891
20	*	12545892
21	*	12545893
22	*	12545894
23	Insurance	12545895
Sheet4

I am trying to fill the table with the document type as follows:

Excel Workbook
	A	B
3	Document Type	Document Number
4	Contract	12545876
5	Contract	12545877
6	Contract	12545878
7	Contract	12545879
8	Contract	12545880
9	Contract	12545881
10	Contract	12545882
11	Contract	12545883
12	Title	12545884
13	Title	12545885
14	Title	12545886
15	Title	12545887
16	Title	12545888
17	Title	12545889
18	Title	12545890
19	Title	12545891
20	Title	12545892
21	Title	12545893
22	Title	12545894
23	Insurance	12545895
Sheet4

The report that I have is very long - more than 200,000 rows. If I try to fill the blank cells with the document type manually, it takes a very long time. Is there a way to try to build a formula in column C to fill the blanks with the appropriate document type ? The formula should search the column and fill the correct document type accordingly.

Is this possible ? Appreciate all the help.

 

[sheetspread]

http://www.mrexcel.com/forum/excel-questions/685157-range-offset-help.html

(Post #10)

 

[jtakw]

Hi,

If your Document Types as indicated by * are the same as the named Document Type above, then you can just, for example, select "Contract", drag the handle at the lower right corner of the cell and drag down as far as necessary, repeat for other Document Types.

 

[AlKey]

Remove asterisks from Col A then use formula
In C2 and copy down
=LOOKUP("zzz",A$2:A2)

Book1
	A	B	C
1	Document Type	Document Number	New Column
2	Contract	12545876	Contract
3		12545877	Contract
4		12545878	Contract
5		12545879	Contract
6		12545880	Contract
7		12545881	Contract
8		12545882	Contract
9		12545883	Contract
10	Title	12545884	Title
11		12545885	Title
12		12545886	Title
13		12545887	Title
14		12545888	Title
15		12545889	Title
16		12545890	Title
17		12545891	Title
18		12545892	Title
19		12545893	Title
20		12545894	Title
21	Insurance	12545895	Insurance
Sheet4

 

[jtakw]

If you really want a formula, then try this:

Excel 2010
	A	B	C
1	Document Type	Document Number
2	Contract	12545876	Contract
3	*	12545877	Contract
4	*	12545878	Contract
5	*	12545879	Contract
6	*	12545880	Contract
7	*	12545881	Contract
8	*	12545882	Contract
9	*	12545883	Contract
10	Title	12545884	Title
11	*	12545885	Title
12	*	12545886	Title
13	*	12545887	Title
14	*	12545888	Title
15	*	12545889	Title
16	*	12545890	Title
17	*	12545891	Title
18	*	12545892	Title
19	*	12545893	Title
20	*	12545894	Title
21	Insurance	12545895	Insurance
Sheet1

Cell Formulas
Range	Formula
C2	=IF(A2="*",C1,A2)

Formula copied down.

 

[sheetspread]

They're not asterisks, but blanks (a Jeanie flaw)

 

[jtakw]

They're not asterisks, but blanks (a Jeanie flaw)

Thanks for the info, sheetspread.

In that case, change my formula to:

Excel 2010
	A	B	C
1	Document Type	Document Number
2	Contract	12545876	Contract
3		12545877	Contract
4		12545878	Contract
5		12545879	Contract
6		12545880	Contract
7		12545881	Contract
8		12545882	Contract
9		12545883	Contract
10	Title	12545884	Title
11		12545885	Title
12		12545886	Title
13		12545887	Title
14		12545888	Title
15		12545889	Title
16		12545890	Title
17		12545891	Title
18		12545892	Title
19		12545893	Title
20		12545894	Title
21	Insurance	12545895	Insurance
Sheet1

Cell Formulas
Range	Formula
C2	=IF(A2="",C1,A2)

Formula copied down.

 

[sheetspread]

This can be done in situ:

Excel 2010
	A	B
1	Document Type	Document Number
2	Contract	12545876
3		12545877
4		12545878
5		12545879
6		12545880
7		12545881
8		12545882
9		12545883
10	Title	12545884
11		12545885
12		12545886
13		12545887
14		12545888
15		12545889
16		12545890
17		12545891
18		12545892
19		12545893
20		12545894
21	Insurance	12545895
Sheet12

highlight A2:A21, F5-Special-Blanks type =A2, ctrl-enter:

Excel 2010
	A	B
1	Document Type	Document Number
2	Contract	12545876
3	Contract	12545877
4	Contract	12545878
5	Contract	12545879
6	Contract	12545880
7	Contract	12545881
8	Contract	12545882
9	Contract	12545883
10	Title	12545884
11	Title	12545885
12	Title	12545886
13	Title	12545887
14	Title	12545888
15	Title	12545889
16	Title	12545890
17	Title	12545891
18	Title	12545892
19	Title	12545893
20	Title	12545894
21	Insurance	12545895
Sheet12

 

[AlKey]

For those who has Excel version 2013 or higher can use Flash Fill an excel feature.
Simply highlight range A2:A21 and click on Flash Fill