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.

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



"Narnimar" wrote:

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.

Thank you Francis,
Offcourse vlookup for B column is good enough. The vlookup is very close to
my concept to get value in A but does return the next nonblank B cell also
which I dont want if I drag the formula down. I would like to have a formula
to shorten the list of my database sheet.
If the value existing in column B1 the column A1 to be displayed, if B is
blank it should search return next value of A corresponding to next nonblank
B whereever down in the column. So that here the Train, Flight and Horse are
blank which will be filtered. I cant use autofilter due to some reasons. If
you need further clarification please revert back.

A B
Car 2
Bus 1
Cycle 20




"Francis" wrote:

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



"Narnimar" wrote:

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.

Filter with non blanks and copy those values.


On Jul 18, 10:32*pm, Narnimar
wrote:
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.

Take a look this site

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


"Narnimar" wrote:

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.

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.

"Teethless mama" wrote:

Take a look this site

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


"Narnimar" wrote:

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.

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


"Narnimar" wrote in message
...
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.

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?

"Hans Knudsen" wrote:

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


"Narnimar" wrote in message
...
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.

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.

--
HTH,

RD

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

"Narnimar" wrote in message
...
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?

"Hans Knudsen" wrote:

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


"Narnimar" wrote in message
...
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.

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

"Ragdyer" wrote:

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.

--
HTH,

RD

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

"Narnimar" wrote in message
...
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?

"Hans Knudsen" wrote:

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


"Narnimar" wrote in message
...
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.

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.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Narnimar" wrote in message
...
Hi Ragdyer,
yes, but it does not display Cycle as 3rd item! which is the next non
blank.
Thank you.

"Ragdyer" wrote:

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.

--
HTH,

RD


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

--------------------------------------------------------------------------
-

"Narnimar" wrote in message
...
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?

"Hans Knudsen" wrote:

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


"Narnimar" wrote in message
...
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.

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



"Narnimar" wrote in message
...
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?

"Hans Knudsen" wrote:

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


"Narnimar" wrote in message
...
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.