×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel 2016 macros - stop to ask for cell range

Excel 2016 macros - stop to ask for cell range

Excel 2016 macros - stop to ask for cell range

(OP)
Hello

I've mostly dealt with macros that go from A to B to C without having to stop for user input so this is a new thing for me.


What I'm doing is adding in names against a record, but it's not a specific name it could be A or B. At the moment I'm doing copy/paste and the repetition is causing physical problems.

Here is an example:

L                      M

2                     Mary Smith
3                     John Brown
4                     Matthew White
5                     Amy Jones

 

Where at this point column L is all blank.

the result should be:

L                                             M

2    John Brown or Matthew White               Mary Smith
3    Mary Smith or Amy Jones                   John Brown
4    Mary Smith or Amy Jones                   Matthew White
5    Jon Brown or Matthew White                Amy Jones

 


I'm wondering about using concatenate : =concatenate(M2," or ",M5) to give the result "Mary Smith or Amy Jones" I could use a small macro to type in the formula each time, but what I'd want to do is have it stop and ask me what cell number to use, and I could type M2 or click in the appropriate cell.

The way these are organised, there is no guarantee that the names would appear in a orderly fashion. These are actually birth marriage and death records I'm trying to organise.

Can someone point me in the right direction? There are over 30,000 lines in this one spreadsheet and I've a dozen more spreadsheets, so anything that will help speed things up and lessen the RSI would be appreciated!

thank you for helping

____________
Pendle

RE: Excel 2016 macros - stop to ask for cell range

Not sure you need a macro, b/c you could type your formula, and copy it down.

If you do need a macro, of course you can get help here on how to do that.

For one thing, you want to specify the cell. Would you be opposed to having Excel figure out the cell based on your actively selected cell? For instance, you could select where you want to put the first formula, then click your button to start the macro, and have it start with ActiveCell.

I may not get to reply much today, but I thought I'd at least help give a start.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

RE: Excel 2016 macros - stop to ask for cell range

Also, I'm not sure I'm totally following the logic of what you're trying to do. What you said does not totally match up with your examples, best I can tell.

Could you type up EXACTLY what you're starting with (of course, changing to fake names, etc) and then type EXACTLY what you intend to end up with?

On the surface, I don't think what you want to do is all that difficult, but I could be totally missing something.

To me, it looks like you want to combine names from different rows/IDs into one row/ID using the grammatical conjunction, "or". But that's not what it sounded like to me in your statements.

Either way, you came to the right place to get the right answer. wink

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

RE: Excel 2016 macros - stop to ask for cell range

Rather than telling us HOW you think something ought to be done (in this case, some specified concatenation) just tell us WHAT it is that you need to accomplish and maybe even the WHY.

Plz don't say, "I need to put a variable number of names on the same row!"

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel 2016 macros - stop to ask for cell range

(OP)
Hello

What I have in my spreadsheet is the index to a register of marriages - thousands of them.


Entries look like this:

Year       Surname         Name           From                Married at    Entry no Spouse             Concatenated name (just for copy/paste purposes)
1901       Smith           Mary           Douglas             RO            459                         Mary Smith
1901       Watson          John           Ramsey              RO            459                         John Watson
1901       Jones           William        Douglas             RO            459                         William Jones
1901       Green           Elizabeth      Douglas             RO            459                         Elizabeth Green

 

As it stands, there is no way to tell who the spouse could be. These examples are together, but they could in reality be hundreds of lines apart. I know that RO is registry office and the numbers are the entries in the actual register.

So what I've been doing is matching the spouses. But as there are two certificates to each page and therefore four people, it could be this person OR that person.

So I have the person's name properly written at the end of the entry as shown above, but I'm manually copy/paste or moving them into the "spouse" column I created, so it looks like this:


Year       Surname         Name           From                Married at    Entry no Spouse
1901       Smith           Mary           Douglas             RO            459      John Watson or William Jones            
1901       Watson          John           Ramsey              RO            459      Mary Smith or Elizabeth Green           
1901       Jones           William        Douglas             RO            459      Mary Smith or Elizabeth Green              
1901       Green           Elizabeth      Douglas             RO            459      John Watson or William Jones                  

 

I can't assume that every number 456 is part of the same record as there will be other places someone is married that might have the same number. Plus I've been transcribing these for the last 10 years from appalling handwriting so some need to be further checked.

So basically I'm looking for a way to make this current copy/paste action a bit easier on my arthritis. I publish these on my genealogy website free of charge for people to search - so I'm trying to provide a service as well!

thank you for helping

____________
Pendle

RE: Excel 2016 macros - stop to ask for cell range

So these identical Entry no rows may be many rows apart? How about taking the Married At value into consideration. then will RO 459 exclude other 459 rows?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel 2016 macros - stop to ask for cell range

(OP)
Hi

Yes, many rows apart. For example, one I've just done with 'Married at' being RO and an Entry number of 15 are on lines 761, 2739, 5116 and 6053


thank you for helping

____________
Pendle

RE: Excel 2016 macros - stop to ask for cell range

Attached is a solution with these modifications to your workbook:
1) a Male/Female (MF) column added and filled
2) a Microsoft Query query added to sheet QUERY
3) a WORKSHEET that mirrors the Marriages sheet where the Spouse is calculated
4) a Function that supplies Married at, Entry no and MF to the query and concatenates the result

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel 2016 macros - stop to ask for cell range

(OP)
thank you - I shall give this a try

thank you for helping

____________
Pendle

RE: Excel 2016 macros - stop to ask for cell range

Simply take your marriages table data and paste it into my Marriages sheet making sure that my headings do not change, that is that the heading values remain as is, although the headings may end up in different columns.

Then copy that table to the WORKSHEET sheet.

SAVE my workbook before proceeding to add formula to rows.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel 2016 macros - stop to ask for cell range

(OP)
Just to say I haven't had a chance to sit down to this yet, but I'll let you know how I get on.

thank you for helping

____________
Pendle

RE: Excel 2016 macros - stop to ask for cell range

Take your time. I have oodles of it. 😉

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel 2016 macros - stop to ask for cell range

Just to use excel 2016+ new built-in get&transform feature (Power Query or M language, in MS Power BI), no VBA solution:
1) convert input data to table, add to Power Query environment;
2) create support query: create full names, group data, combine full names to list, merge list with custom delimiter, add opposite gender to match,
3) combine queries, output to excel table.

I used Skip's input data with gender column and added working sheet to his workbook ("Marriages (2)"), however the queries use only added sheet and work in plain "xlsx" workbook, without VBA project.

combo

RE: Excel 2016 macros - stop to ask for cell range

(OP)
Hello - sorry to have taken so long - all is working just fine and saving me lots of time!

Thank you everyone for your help.

thank you for helping

____________
Pendle

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close