Cell Data by Column

[Guest]

Trying to figure out an equation -- I need cell A1 to fill-in by data from
column B, if B1 has text fill in A1, if B1 doesnt have text, move to cell B2,
etc. etc,,,,but if B1 has text and fills in A1, need A2 to replicate A1
procedure starting with B2, etc,etc. If someone can help me out or let me
know if it is even possible I would appreciate it.

 

[Guest]

I had a bit of a tough time knowing exactly what you want from the info you
gave. (As in what do you want to put in A1 if B1 is not text.)

Give this a try in anycase and see if it is what you want. Put this formula
in A1 and then copy it down for as many rows of B as you have. IF the B cell
is test it will display that text in A, if not it just sticks a blank in A.

=IF(ISTEXT(B1),B1,"")

 

[Guest]

Sorry about the bad explination, Looking to have A1 scan down col. B for
text. Once it finds texts copy that text to A1, then have A2 scan down col.
B for text, etc. etc. Overall goal is to make a detailed invoice summary
sheet that will scan each invoice page for the line items without having to
look at each invoice page by page. I really appreciate the help and dont
know if this is possible, but have struggled with it for 3 days.

 

[Ildhund]

You're not really giving us enough information to be able to give a
comprehensive answer, but assuming that column "B" contains only cells
that are either blank or contain text, the following may help you
achieve your aim:

1. In the top cell of a spare column (I used "C"), put
=COUNTA($B$1:$B1)
and copy down to the end of your data.

2. In A1 put
=IF(ISERROR(MATCH(ROW(),$C$1:$C$1000,0)),"",INDEX($B$1:$B$1000,MATCH(ROW(),$C$1:$C$1000,0)))
and copy down. You will have to (a) change "C" to the column you used in
step 1 and (b), if necessary, change 1000 to a number higher than the
number of rows your data occupy.

This will concentrate the texts in column "B" in order at the top of
column "A", which is what I imagined you were trying to do.

If you want to know what these formulae do, then step 1 provides a
running total of the number of text cells. Step 2 uses MATCH to look
down this list of running totals until it finds a number the same as the
row number [ROW()] - i.e. on row 3 it looks for the first "3" in column
"C", which is for example on row 20. INDEX then finds the text in B20.
The ISERROR bit just puts a blank ("") instead of an irritating #N/A
when all the texts have been accounted for.

Does this help?

Noel

in