NUMBER IF MATCH List with no Blanks, NEED HELP!

[wwest]

Help!!! I need a formula for this case: In sheet 1, there are two columns A and B. If a number is inserted in column B, the matching projectno. in column A needs to be transferred to sheet 2. Blank cells has to be skipped so there will be a list of projectno. which will activate if the number is filled in, in sheet 1.
sheet1			sheet2
projectno.	number		projectno.
E1	88888		E1
E2			E4
E3			N4
E4	44444		O2
E5
N1
N2
N3
N4	77777
N5
O1
O2	33333
O3
O4
O5

<tbody>
</tbody>

 

[FDibbins]

I know there are other ways to do this, but this method is fairly simple and easy to understand. It uses a helper column (which you can hide)...

	A​	B​	C​	D​
1​	sheet1			sheet2
2​
3​	projectno.	number		projectno.
4​	E1	88888	1​	E1
5​	E2		1​	E4
6​	E3		1​	N4
7​	E4	44444	2​	O2
8​	E5		2​
9​			2​
10​	N1		2​
11​	N2		2​
12​	N3		2​
13​	N4	77777	3​
14​	N5		3​
15​			3​
16​	O1		3​
17​	O2	33333	4​

C4=COUNTIF($B$4:B4,"<>")
copied down

Then to pull your data...
D4=IFERROR(INDEX(A:A,MATCH(ROW(A1),C:C,0)),"")
copied down

 

[AhoyNC]

Method without helper column.
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A
1	projectno.
2	E1
3	E4
4	N4
5	O2
6
Sheet2

Excel Workbook
	A	B
1	projectno.	number
2	E1	88888
3	E2
4	E3
5	E4	44444
6	E5
7
8	N1
9	N2
10	N3
11	N4	77777
12	N5
13
14	O1
15	O2	33333
16	O3
17	O4
18	O5
Sheet1

 

[wwest]

Thank you very much, Ford!

Is there also a formula without a helper column? It's for a timesheet I am developing which will be used by a lot of workers. The basic data field and monthly sheets are ready and I only need to fit in this last formula.
Thanks!

 

[wwest]

Ahhh...thank you! I hope this will work. I will let you know

 

[wwest]

It did work! thank you so much.

I have one other question. Sometimes the projectnumber in column B contains some text, like 777777-ERC-9999. The fomula doesn't work if there are letters in de formula. Is there a formula which covers numbers and also letters?

You already helped me a lot further......I have been searching for days! THANKS

 

[FDibbins]

While that ARRAY formula will indeed eliminate the need for the helper column, if you have a LOT of data to go through, it could tend to slow your file down

 

[FDibbins]

It did work! thank you so much.

I have one other question. Sometimes the projectnumber in column B contains some text, like 777777-ERC-9999. The fomula doesn't work if there are letters in de formula. Is there a formula which covers numbers and also letters?

You already helped me a lot further......I have been searching for days! THANKS

try this ARRAY formula...

	A​	B​	C​
1​	sheet1
2​
3​	projectno.	number
4​	E1	88888	E1
5​	E2		E4
6​	E3		N4
7​	E4	44444	O2
8​	E5
9​
10​	N1
11​	N2
12​	N3
13​	N4	77777
14​	N5
15​
16​	O1
17​	O2	33333
18​	O3
19​	O4
20​	O5

C4=IFERROR(INDEX(A:A,SMALL(IF($B$4:$B$20<>"",ROW($B$4:$B$20)),ROWS($A$1:A1))),"")
CSE entered, then copied down

 

[Aladin Akyurek]

@wwest

I'd prefer the set up AhoyNC suggests. He used IFERROR for control. If you need more speed, this slow function can be replaced...

Since it's known where the data is located, define the following names in Formulas | Name Manager...

Projects as referring to Sheet1!$A$2:$A$18;

Numbers as referring to Sheet1!$B$2:$B$18; and very importantly:

Ivec as referring to:

=ROW(Peojects)-ROW(INDEX(Projects,1,1))+1

Sheet2

In A1 of Sheet2 just enter:

=COUNT(Sheet1!B2:B18)

In A2 of Sheet2 just enter: projectno.

In A3 of Sheet2 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$3:A3)>$A$1,"",INDEX(Projects,SMALL(IF(ISNUMBER(Numbers),Ivec),ROWS($A$3:A2))))

Note 1. Using rather ISNUMBER than anything else is appropriate here for we have to do with numbers and nothing else.

Note 2. The ROWS bit in front is faster than IFERROR.

Note 3. Ivec is a generalizable concept and probably computed just once in the name once, therefore fast.