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

[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.

 

[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

 

[muddan madhu]

Filter with non blanks and copy those values.

 

[Teethless mama]

Take a look this site

http://www.contextures.com/xladvfilter01.html#ApplyAF

 

[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.

 

[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 D13

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

 

[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?

 

[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$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.

 

[Narnimar]

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

 

[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.

 

[Narnimar]

Perfect now!! I fixed the error while copy from webpage. Thank you so much to
you!

 

[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

 

[RagDyer]

Glad you got it working, and thanks for the feed=back.

 

[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

 

[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

 

[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

 

[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. A27 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!!