Vlook up nonblank in B:B and return in A columns

N

Narnimar

I got a list in B:B in which there are values in but some are blank. I need a
if formaula can summerize it in A column with only those which are
nonblanks.
A B
Car 2
Bus 1
Train blank
Flight blank
Cycle 20
Horse Blank

the sumery should apear just like -
A B
Car 2
Bus 1
Cycle 20

Any Help is highly appreciated.
 
F

Francis

Hi

not following you very well

if in your summary, you have in col A the following
A B
Car
Bus
Cycle

a simple vlookup such as the below formula will do

=VLOOKUP(A8,$A$1:$B$6,2,0)

otherwise, pls elaborate
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another
 
N

Narnimar

Teethless mama,
I have gone through the site you advised, the concept which I got in my work
sheet is similar to the autofilter but I need formula. I have seen before
somewhere a Bill of material sheet in which the formula was picking the
nonblanks and dispalying it from a huge datasheet. I do not know what trick
it has been used but interesting. Once I learn it then I have plan to do the
a bill of material in which I select a Product then the list of ingradients
appear in the print area. Thanks for your interest.
 
H

Hans Knudsen

I understand you as follows:

You have
Car, Bus, Train, Flight, Cycle and Horse in A1:A6 and 2, 1, blank, blank 20
and blank in B1:B6, and you want to have

Car, Bus, Cycle in C1:C3 and 2, 1 and 20 in D1:D3

If my understanding is correct you can enter in D1 (array formula):
=IF(ROW(A1)>COUNTA(B:B);"";INDEX(B:B;SMALL(IF(B$1:B$20<>"";ROW($1:$20));ROW(A1))))
and in C1:
=IF(ROW(A1)>COUNTA(B:B);"";INDEX($A$1:$A$20;MATCH(D1;$B$1:$B$20;0)))
Copy down as required.

Hans
 
N

Narnimar

Hi Hans Knudsen,
Thank you. Your formula in D works great!! But I think index function in C
returns wrongly when the I put equal value for b1and b2. For both I put 2 and
tested, then it return car in both c1 and c2. The c2 is to be Bus. Do we have
another trick to fix it?
 
R

Ragdyer

Try this *array* formula in C1 and copy down:

=IF(ROWS($1:1)>COUNTA(B$1:B$20),"",INDEX(A$1:A$20,LARGE(IF(B$1:B$20=D1,ROW($
1:$20)),COUNTIF(D1:D$20,D1))))

WATCH OUT - my separators are commas!
--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.
 
N

Narnimar

Hi Ragdyer,
yes, but it does not display Cycle as 3rd item! which is the next non blank.
Thank you.
 
R

Ragdyer

It works fine for me!

Make sure your ranges and your relative and absolute references are correct.

Post the *exact* formula that you're using.
 
H

Hans Knudsen

You are right. Try this array formula in C1:
=IF(ROW(A1)>COUNTA(B:B);"";INDEX(A:A;SMALL(IF(B$1:B$20<>"";ROW($1:$20));ROW(A1))))
(In my previous post I forgot to say that I use semikolon as argument
separator).

Hans
 
N

Narnimar

look up in column and return a nonblank list:

I have further need to get the small list when there is added rows like
below for the same. What my idea is I should now hookup in H1 from A column &
return nonblanks list from C or D and its value in F & E column respectively
.. So when nonblank list needed from C column or D column what would be the
formula? Thanks in advance.

A B C D E F Car 2
blank 5
Bus 1 2 1
Train blank blank 3
Flight blank blank 1
Cycle 20 35 Blank
Horse Blank 1 2
 
N

Narnimar

Hi Ragdyer/Hans,
I thought I should be more clear to you with my question.
I have further need to get the small list when there is added rows like
below for the same. What my idea is I should now lookup at A column from H1
&
return nonblanks list from C or D and its value in F & E column respectively
So when nonblank list needed in F & E based on the H1 (will be equal to
either C or D or E...) from C column or D column what would be the
formula? Hope I have improved the question. Please feel free to reply if
not yet clear. Thanks in advance.

A B C D E F
Car 2 blank 5
Bus 1 2 1
Train blank blank 3
Flight blank blank 1
Cycle 20 35 Blank
Horse Blank 1 2
 
R

RagDyeR

I don't see your problem.

If I understand your question, you already have all the formulas you need!

All you have to do is revise the column references to get the additional
values.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Hi Ragdyer/Hans,
I thought I should be more clear to you with my question.
I have further need to get the small list when there is added rows like
below for the same. What my idea is I should now lookup at A column from H1
&
return nonblanks list from C or D and its value in F & E column
respectively
So when nonblank list needed in F & E based on the H1 (will be equal to
either C or D or E...) from C column or D column what would be the
formula? Hope I have improved the question. Please feel free to reply if
not yet clear. Thanks in advance.

A B C D E F
Car 2 blank 5
Bus 1 2 1
Train blank blank 3
Flight blank blank 1
Cycle 20 35 Blank
Horse Blank 1 2
 
N

Narnimar

Dear RagDyeR,
Thank you for you interest!
Here the new cell is H1 where I chose the column names like C or D as per
need. A2:D7 are the total data range. I just type the column name"C" or "D"
in H1 then the formulas in colum F and E should return the nonblanks same as
before. For example the formulas should hlookup (I think) for the letter/text
C in H1 and in row 1:1 then return the nonblanks in column F. And accordingly
E always from A only. Slighltly advanced problem but I could't do it!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top