Hello,

I'm having a problem concatenating two columns.
I have 2 empty columns, which I then want to concatenate once
information is populated into these fields. I then want to create a
list from these two concatenated columns and have this list as a lookup
on a different worksheet.
When I create the list I highlight the entire two columns as I don't
have a predefined number of entries for the two columns.

I've done this already but when I try and lookup any of the information
I get a blank list.


Can someone please advise me on any course of action
Do I need to use an isblank method?
Many many thanks


--
Max_power
------------------------------------------------------------------------
Max_power's Profile: http://www.excelforum.com/member.php...o&userid=32255
View this thread: http://www.excelforum.com/showthread...hreadid=530903

I would only put the formula in rows that have entries--I'd copy the formula
down when I added more data.

But maybe you could just copy the formula down a bit more than you need and make
the formula return "" if the other data isn't there (yet).

=if(counta(a1:b1)=0,"",vlookup(a1&b1,sheet2!a:e,5, false))
or
=if(counta(a1:b1)<2,"",vlookup(a1&b1,sheet2!a:e,5, false))

(depending if filling one of those cells is sufficient.)

========
Another alternative to using concatenate...

You may be able to use something like:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0))
(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

(still an array formula)

Max_power wrote:

Hello,

I'm having a problem concatenating two columns.
I have 2 empty columns, which I then want to concatenate once
information is populated into these fields. I then want to create a
list from these two concatenated columns and have this list as a lookup
on a different worksheet.
When I create the list I highlight the entire two columns as I don't
have a predefined number of entries for the two columns.

I've done this already but when I try and lookup any of the information
I get a blank list.

Can someone please advise me on any course of action
Do I need to use an isblank method?
Many many thanks

--
Max_power
------------------------------------------------------------------------
Max_power's Profile: http://www.excelforum.com/member.php...o&userid=32255
View this thread: http://www.excelforum.com/showthread...hreadid=530903

--

Dave Peterson

Many thanks for your detailed response.

I've got a new query now that is similar to the previous question.

I have a forumula in Column B2 of my worksheet

=CONCATENATE(C2,\":\",F2,\";\",E2,\";\",H2,\";\",K 2,\" - \",P2)

When I copy this formula down through a number of cells I get the
output

:;;; -


What I'm looking for is a formula to say if the cell is blank(empty)
don't concatenate the cells just return a null value ie leave it empty
and don't return *:;;; -
*

Thanks for any help


--
Max_power
------------------------------------------------------------------------
Max_power's Profile: http://www.excelforum.com/member.php...o&userid=32255
View this thread: http://www.excelforum.com/showthread...hreadid=530903

Maybe you could use something like:

=if(c2="","",c2&":") & if(f2="","",f2&";") & ......

Just break it into smaller pieces.

Max_power wrote:

Many thanks for your detailed response.

I've got a new query now that is similar to the previous question.

I have a forumula in Column B2 of my worksheet

=CONCATENATE(C2,\":\",F2,\";\",E2,\";\",H2,\";\",K 2,\" - \",P2)

When I copy this formula down through a number of cells I get the
output

:;;; -

What I'm looking for is a formula to say if the cell is blank(empty)
don't concatenate the cells just return a null value ie leave it empty
and don't return *:;;; -
*

Thanks for any help

--
Max_power
------------------------------------------------------------------------
Max_power's Profile: http://www.excelforum.com/member.php...o&userid=32255
View this thread: http://www.excelforum.com/showthread...hreadid=530903

--

Dave Peterson

Thank you very much for your assistance


--
Max_power
------------------------------------------------------------------------
Max_power's Profile: http://www.excelforum.com/member.php...o&userid=32255
View this thread: http://www.excelforum.com/showthread...hreadid=530903