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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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

"Ragdyer" wrote:

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.

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

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.

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

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Narnimar" wrote in message
...
Perfect now!! I fixed the error while copy from webpage. Thank you so much
to
you!

"Ragdyer" wrote:

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.

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

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


"Ragdyer" wrote:

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.

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

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

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

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 !
-----------------------------------------------------------------------------------------------

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

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

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!!


"RagDyeR" wrote:

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 !
-----------------------------------------------------------------------------------------------

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