append 2 cells, delete old
 

Hey guys and gals,

I have been monkeying around with a formula and almost have it doin
what I want. Too bad I didn't have just a few more braincells or
might be able to figure it out.

I constructed a formula to append data from one cell to the cell above
but I can't get it to remove the data from the cell which informatio
was appended from. I sure hope this makes sense.

Here is what my data looks like: (each line is its own row)

12200-HN2-
000
12204-HN2-305
12206-371-
300
12206-371-305
12206-611-300
12206-ML8-300
12251-HN2-003
12252-HN2-
305
12310-HN2-000
12315-HN2-003
12351-HN2-000

16211-HN2-000

from row 2 I want to put the 000 after 12200-HN2- in row 1 and remov
the content of row two, or at least make it have a value of "xxx
literally. That way I could remove all rows that start with "xxx".

here is my formula put into another col, cell 1
=IF(RIGHT(A1,1)="-",A1&A2,A1)

This will indeed append the 000 to the line above, but it leaves th
000 there. Since my data has varying values that get cut and put o
their own line I need to remove them or rename them to somethin
consistent throughout the worksheet.


I know I'm a pain, but I have been at it for a day and a half. I'v
looked all over for something to get me started and can't figure i
out.

Any ideas?

Bill Beecha

--
billbeecha
-----------------------------------------------------------------------
billbeecham's Profile: http://www.excelforum.com/member.php...fo&userid=1628
View this thread: http://www.excelforum.com/showthread.php?threadid=27788

append 2 cells, delete old
 

What you are trying to do is just not going to work out for you that easily
I'm afraid. If you don't mind doing some Copying and pasting values you can
try this...

You currently have 2 columns one with source data and one with final data.
We are going to need 3 columns.

Column 1 is a Test for Suffix only
Column 2 is Source data
Column 3 is Final Data

Final data is the formula that you already have.
Source data is the Original data that your formula works on.
Test will be a formula such as
=find("-", A2) where A2 is the SourceDataCell. Copy this formula down.
Copy and paste special (values) the Final data column of the results or it
will become be messed up when you delete the unwanted rows.
Now sort the Test Column and delete the unwanted rows...
You can also delete the Test column at this point if you like...

If this does not work for you then you probably need to use a VBA macro to
make the changes that you want...

Hope this helps...


"billbeecham" wrote:


Hey guys and gals,

I have been monkeying around with a formula and almost have it doing
what I want. Too bad I didn't have just a few more braincells or I
might be able to figure it out.

I constructed a formula to append data from one cell to the cell above,
but I can't get it to remove the data from the cell which information
was appended from. I sure hope this makes sense.

Here is what my data looks like: (each line is its own row)

12200-HN2-
000
12204-HN2-305
12206-371-
300
12206-371-305
12206-611-300
12206-ML8-300
12251-HN2-003
12252-HN2-
305
12310-HN2-000
12315-HN2-003
12351-HN2-000

16211-HN2-000

from row 2 I want to put the 000 after 12200-HN2- in row 1 and remove
the content of row two, or at least make it have a value of "xxx"
literally. That way I could remove all rows that start with "xxx".

here is my formula put into another col, cell 1
=IF(RIGHT(A1,1)="-",A1&A2,A1)

This will indeed append the 000 to the line above, but it leaves the
000 there. Since my data has varying values that get cut and put on
their own line I need to remove them or rename them to something
consistent throughout the worksheet.


I know I'm a pain, but I have been at it for a day and a half. I've
looked all over for something to get me started and can't figure it
out.

Any ideas?

Bill Beecham


--
billbeecham
------------------------------------------------------------------------
billbeecham's Profile: http://www.excelforum.com/member.php...o&userid=16286
View this thread: http://www.excelforum.com/showthread...hreadid=277885

append 2 cells, delete old
 

in the first row of that other column, put in your current formula (Assume
were are in column C)

=IF(RIGHT(A1,1)="-",A1&A2,A1)

so that would be in C1
then in C2 put in

=IF(RIGHT(A1,1)="-","",IF(RIGHT(A2,1)="-",A2&A3,A2))

Then drag fill that down the column.

--
Regards,
Tom Ogilvy

"billbeecham" wrote in message
...

Hey guys and gals,

I have been monkeying around with a formula and almost have it doing
what I want. Too bad I didn't have just a few more braincells or I
might be able to figure it out.

I constructed a formula to append data from one cell to the cell above,
but I can't get it to remove the data from the cell which information
was appended from. I sure hope this makes sense.

Here is what my data looks like: (each line is its own row)

12200-HN2-
000
12204-HN2-305
12206-371-
300
12206-371-305
12206-611-300
12206-ML8-300
12251-HN2-003
12252-HN2-
305
12310-HN2-000
12315-HN2-003
12351-HN2-000

16211-HN2-000

from row 2 I want to put the 000 after 12200-HN2- in row 1 and remove
the content of row two, or at least make it have a value of "xxx"
literally. That way I could remove all rows that start with "xxx".

here is my formula put into another col, cell 1
=IF(RIGHT(A1,1)="-",A1&A2,A1)

This will indeed append the 000 to the line above, but it leaves the
000 there. Since my data has varying values that get cut and put on
their own line I need to remove them or rename them to something
consistent throughout the worksheet.


I know I'm a pain, but I have been at it for a day and a half. I've
looked all over for something to get me started and can't figure it
out.

Any ideas?

Bill Beecham


--
billbeecham
------------------------------------------------------------------------
billbeecham's Profile:
http://www.excelforum.com/member.php...o&userid=16286
View this thread: http://www.excelforum.com/showthread...hreadid=277885