Excel 2016 macros - stop to ask for cell range 1

[pendle666]

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:

[pre ]L M

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

[/pre]

Where at this point column L is all blank.

the result should be:

[pre]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

[/pre]


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

 

[kjv1611]

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

 

[kjv1611]

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.

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

 

[SkipVought]

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,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[pendle666]

Hello

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


Entries look like this:

[pre]
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

[/pre]

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:


[pre]
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

[/pre]

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

 

[SkipVought]

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,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[pendle666]

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

 

[SkipVought]

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,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[pendle666]

thank you - I shall give this a try

thank you for helping

____________
Pendle

 

[SkipVought]

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,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[pendle666]

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

 

[SkipVought]

Take your time. I have oodles of it.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[combo]

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

 

[pendle666]

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