NUMBER IF MATCH List with no Blanks, NEED HELP!

W

wwest

New Member
Joined
Jul 16, 2016
Messages
4
[TABLE="width: 576"]
<tbody>[TR]
[TD="colspan: 9"]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.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sheet1[/TD]
[TD][/TD]
[TD][/TD]
[TD]sheet2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]projectno.[/TD]
[TD]number[/TD]
[TD][/TD]
[TD="colspan: 2"]projectno.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E1[/TD]
[TD]88888[/TD]
[TD][/TD]
[TD]E1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E2[/TD]
[TD][/TD]
[TD][/TD]
[TD]E4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E3[/TD]
[TD][/TD]
[TD][/TD]
[TD]N4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E4[/TD]
[TD]44444[/TD]
[TD][/TD]
[TD]O2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]N1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]N2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]N3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]N4[/TD]
[TD]77777[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]N5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]O1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]O2[/TD]
[TD]33333[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]O3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]O4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]O5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
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)...
[Table="width:, class:grid"][tr][td] [/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][/tr]
[tr][td]
1​
[/td][td]sheet1[/td][td][/td][td][/td][td]sheet2[/td][/tr]

[tr][td]
2​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
3​
[/td][td]projectno.[/td][td]number[/td][td][/td][td]projectno.[/td][/tr]

[tr][td]
4​
[/td][td]E1[/td][td]88888[/td][td]
1​
[/td][td]E1[/td][/tr]

[tr][td]
5​
[/td][td]E2[/td][td][/td][td]
1​
[/td][td]E4[/td][/tr]

[tr][td]
6​
[/td][td]E3[/td][td][/td][td]
1​
[/td][td]N4[/td][/tr]

[tr][td]
7​
[/td][td]E4[/td][td]44444[/td][td]
2​
[/td][td]O2[/td][/tr]

[tr][td]
8​
[/td][td]E5[/td][td][/td][td]
2​
[/td][td][/td][/tr]

[tr][td]
9​
[/td][td][/td][td][/td][td]
2​
[/td][td][/td][/tr]

[tr][td]
10​
[/td][td]N1[/td][td][/td][td]
2​
[/td][td][/td][/tr]

[tr][td]
11​
[/td][td]N2[/td][td][/td][td]
2​
[/td][td][/td][/tr]

[tr][td]
12​
[/td][td]N3[/td][td][/td][td]
2​
[/td][td][/td][/tr]

[tr][td]
13​
[/td][td]N4[/td][td]77777[/td][td]
3​
[/td][td][/td][/tr]

[tr][td]
14​
[/td][td]N5[/td][td][/td][td]
3​
[/td][td][/td][/tr]

[tr][td]
15​
[/td][td][/td][td][/td][td]
3​
[/td][td][/td][/tr]

[tr][td]
16​
[/td][td]O1[/td][td][/td][td]
3​
[/td][td][/td][/tr]

[tr][td]
17​
[/td][td]O2[/td][td]33333[/td][td]
4​
[/td][td][/td][/tr]
[/table]
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
 
Upvote 0
Method without helper column.
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

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


Excel Workbook
AB
1projectno.number
2E188888
3E2
4E3
5E444444
6E5
7
8N1
9N2
10N3
11N477777
12N5
13
14O1
15O233333
16O3
17O4
18O5
Sheet1
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
wwest said:
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
Click to expand...
try this ARRAY formula...
[Table="width:, class:grid"][tr][td] [/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][/tr]
[tr][td]
1​
[/td][td]sheet1[/td][td][/td][td][/td][/tr]

[tr][td]
2​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
3​
[/td][td]projectno.[/td][td]number[/td][td][/td][/tr]

[tr][td]
4​
[/td][td]E1[/td][td]88888[/td][td]E1[/td][/tr]

[tr][td]
5​
[/td][td]E2[/td][td][/td][td]E4[/td][/tr]

[tr][td]
6​
[/td][td]E3[/td][td][/td][td]N4[/td][/tr]

[tr][td]
7​
[/td][td]E4[/td][td]44444[/td][td]O2[/td][/tr]

[tr][td]
8​
[/td][td]E5[/td][td][/td][td][/td][/tr]

[tr][td]
9​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
10​
[/td][td]N1[/td][td][/td][td][/td][/tr]

[tr][td]
11​
[/td][td]N2[/td][td][/td][td][/td][/tr]

[tr][td]
12​
[/td][td]N3[/td][td][/td][td][/td][/tr]

[tr][td]
13​
[/td][td]N4[/td][td]77777[/td][td][/td][/tr]

[tr][td]
14​
[/td][td]N5[/td][td][/td][td][/td][/tr]

[tr][td]
15​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
16​
[/td][td]O1[/td][td][/td][td][/td][/tr]

[tr][td]
17​
[/td][td]O2[/td][td]33333[/td][td][/td][/tr]

[tr][td]
18​
[/td][td]O3[/td][td][/td][td][/td][/tr]

[tr][td]
19​
[/td][td]O4[/td][td][/td][td][/td][/tr]

[tr][td]
20​
[/td][td]O5[/td][td][/td][td][/td][/tr]
[/table]
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
 
Upvote 0
@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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Formula assistance - identifying full or part counts
Replies
5
Views
344
RobP
R
O
  • Solved
Index/Match, Nested IF statements, or Array Formula?
Replies
9
Views
632
Ol Reliable
O
H
  • Solved
Index and match formula that ignores dynamic value
2
Replies
14
Views
1K
FormR
FormR
E
  • Solved
Filter and Textjoin formula if two columns match a text value in a cell - excel 365 in a Mac
Replies
7
Views
420
Fluff
Fluff
gtd526
  • Solved
Get the Row number for Matched cell value
Replies
2
Views
433
gtd526
gtd526

Share this page

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