Combining two columns into one with reformatting

Topic

New Reply

My wife does some volunteer work for a local genealogy group who are currently building databases of tombstones in graveyards around the city and in some smaller outlying communities using Excel. The current spreadsheet has four columns with the following headers… |Death Date|Burial Date|Section|Plot| The working group has decided that they wish to reformulate these four columns into two as follows:

|Death Date|Burial Date| into one column called “Burial Data” with each cell entry to have two lines in each cell, e.g., “Died 19 Jun 1972/Buried 21 Jun 1972”. The dates are already in adjoining cells in a date format, so what I am asking…is whether or not there is an easy way to transform these two columns into one column and add the additional labels “Died” and Buried” in front of the dates.

The Section and Plot data are currently in numeric format and the working group wish to combine these two columns into one column with the Header “Section:Plot” which effectively tells someone where in the graveyard a person is buried. They want each entry in each cell to look something like 1:001 or 1:065, etc. Again, is there a relatively simple way to do this?

When I say “simple”, I really mean is there a way to do this without having to write a complex (or even simple) macro to do this (which I am not qualified to do), or are there some simple macros available, in the public domain, that would at least combine the data from each set of two columns into one column that could then be subjected to further editing…anything that would help to automate all or part of the process would be helpful. Any insight or help that anyone would care to offer will be greatly appreciated by both me and my wife. Thanks in advance.

Ron M

Reply | Quote

Replies

If you have the Date Death in A2, the Burial Death in B2, the section in C2, and the plot in D2, then you can create a formula in a cell in that row for the Death/burial
=”Died “&TEXT(A2,”d mmm yyyy”)&CHAR(10)&”Buried “&TEXT(B2,”d mmm yyyy”)

[note: Char(10) is a linefeed character to have in cell wrapping]

and another for the section plot
=C2&”:”&TEXT(D2,”000″)

These can be copied down the column to create the 2 new combined columns. The death/burial must have [format cells – alignment(tab) – wrap text ] checked for it to wrap.

If desired (though I do not recommend) you can copy – paste-special values these columns and then delete the other 4. Combining and eliminating the originals make it more difficult to filter on dates, sections and plots.

Steve
PS see the attached example

Reply | Quote

If you have the Date Death in A2, the Burial Death in B2, the section in C2, and the plot in D2, then you can create a formula in a cell in that row for the Death/burial
=”Died “&TEXT(A2,”d mmm yyyy”)&CHAR(10)&”Buried “&TEXT(B2,”d mmm yyyy”)

[note: Char(10) is a linefeed character to have in cell wrapping]

and another for the section plot
=C2&”:”&TEXT(D2,”000″)

These can be copied down the column to create the 2 new combined columns. The death/burial must have [format cells – alignment(tab) – wrap text ] checked for it to wrap.

If desired (though I do not recommend) you can copy – paste-special values these columns and then delete the other 4. Combining and eliminating the originals make it more difficult to filter on dates, sections and plots.

Steve
PS see the attached example

Thanks Steve, I appreciate your prompt reply and the example spreadsheet. I do realize that combining the originals and eliminating them will make it more difficult to filter on dates, sections, plots etc. and I shall point that out to the people involved before we undertake any of these mods. I suspect, that they do not realize the potential problems that they may create, but I will tell them – informed consent and all that. I don’t want to be in a situation where they come back to me and say “Well you didn’t tell us this would happen…”

Just one point of clarification – (it’s been so long since I have had to do anything like this in Excel) – once I have the new columns, i.e., the new columns based on your formulas, then they will still be dependent on the old columns through the formulas, so I should then copy them into two new columns where only the values are pasted and then delete the six others – the original 4 + the 2 that were created from your formulas, thus removing any dependence on the “formulas”. Have I interpreted this correctly?

Again, thanks for your help with this – it is appreciated, by me and my wife.

Ron M

Reply | Quote

I should then copy them into two new columns where only the values are pasted and then delete the six others – the original 4 + the 2 that were created from your formulas, thus removing any dependence on the “formulas”. Have I interpreted this correctly?

Yes that is about what I meant by “you can copy – paste-special values these columns and then delete the other 4”. Instead of pasting onto 2 new cols, you can paste the values onto the existing columns and then delete the 4 original ones. But copying to 2 new ones and deleting the 6 results in the same at the end…

I would suggest that you consider the idea of keeping the 4 columns (and what I presume are also other columns) in some database and just keep these formulas in mind for some “output” or results screens of some extracted data from the database (like some customer statement).

[It is the same with names and addresses. I would keep first, Mid, last names, address, state and zip all separate, but when desired they can be combined into an address label format or listed lastname comma first name, etc. Once combined it is very difficult to work with that information. Instead of filtering or sorting by last name, state, zip etc.

Combining the separate items is so much easier than trying to separate them later…]

Steve

Reply | Quote

Thanks Steve. I pointed out the potential problems to the folks I am working with and they wanted some time to think about it – the changes, that is. I did a little test on the COPY + PASTE SPECIAL into two new columns and discovered that it will work with one exception, the “CHAR(10)” for the linefeed to have cell wrapping also disappears in the “PASTE SPECIAL” operation, so the two dates are back on one line with no spacing between them, so it looks like “Died 19 Jun 1972Buried 21 Jun 1972” on one contiguous line. Is there any way to preserve the line feed to give the wrapped cell during the “COPY+PASTE SPECIAL” operation? Thanks.

Ron M

Reply | Quote

The paste special should not remove the linefeed character. [Unless this is a new “feature” of XL2007 and XL2010. It did not occur with versions from XL97-XL2003. I do not have the newer versions to test it…]

Steve

Reply | Quote

The paste special should not remove the linefeed character. [Unless this is a new “feature” of XL2007 and XL2010. It did not occur with versions from XL97-XL2003. I do not have the newer versions to test it…]

Steve

All is well Steve…we have it all worked out. As it turns out, they wanted these dates all in one line, so we were able to substitute the CHAR(10) with some blank spaces to make it readable. Again, thank you very much for your help with this.

Ron M

Reply | Quote

If you want to preserve the 2-line format:
use a symbol such as $ (or anything else unlikely to occur in the expressions being joined);
use Edit Replace to replace the $ with alt+010 (hold down the alt key, type 010 on the numeric keypad.
Remember to Align the cells as Wrap Text

Reply | Quote

I would like to thank all parties for the question and answer process used in this thread. The question was articulated very well. I have seen questions where I had no clue what was really being asked and what result was needed. This question left no doubts as to what the problem was and how it needed to be solved. The answers were also articulated very well. One could follow the instructions and the logic without any problem whatsoever. I would recommend that this posting be used as an example of how to ask a question as well as how to answer a question. Thanks to all.

Reply | Quote

=CONCATENATE(“Died “,A1,”/Buried “,B1)

Reply | Quote

=CONCATENATE(“Died “,A1,”/Buried “,B1)

I agree with txbjones’ post that this thread is an example of an “excellent question and answer process.”

Your cryptic post, however, represents the other end of the scale. With no explanation whatsoever, it represents an answer only to those that already know the answer.

A simple sentence such as “Here is an alternative formula” would have created a good answer in keeping with the rest of this otherwise outstanding Q&A thread.

Sorry, I just believe if you have something to say, you should take the time to say it WELL. Others did and that’s what prompted txbjones to make the comment.

Reply | Quote

If they read all the replies before mine, it should be obvious to any reasonably sentient being. Does that use a sufficient number of words to convey my meaning?

Reply | Quote

Loungers, please be polite. Less proficient Loungers may not understand a terse reply without context and directions for use. If you get a reply you don’t understand, experiment with it and ask for clarification. Criticism and sarcasm don’t help.

If the date cells use Excel’s serial date number system (where 1/1/1900 is day 1) references to those cells must be formatted using using TEXT(A1,”DDDD M, YYYY”) or a similar format, as Steve explains in [post=791588]post 791588[/post]; without that formatting CONCATENATE will return (display) the serial dates as general numbers in the form “Died 26469 /Buried 26471”. Check out the TEXT function in the Help if you aren’t familiar with it.

Reply | Quote