Hello,
I am having a lot of difficulty reorganizing an input spreadsheet into an output spreadsheet using only formulas. I attempted to use various IF statements to reorganize this how I wanted but after hours with no avail.
The problem I have is a spreadsheet where contact information and details are copied in from a different sheet and must be reorganized to the correct format conditional on their address, email, and phone type.
For example, the sheet generally has two types of addresses (business and home) each with respective address details (street1, street 2, city, state, phone, country). The output sheet must organize all business addresses in one set of columns and all home addresses in another set of columns without displaying what type it is. Similarly for phone numbers, all business phone numbers must be in one column and all home phone numbers in another without displaying what type they actually are (Note- Some phone numbers are "mobile phone": is it possible to have these to output to home and be overwritten if home already exists?). Then finally again, all "main email"s must be in the work email and all "personal email"s must be grouped in the home email column in the output. If it helps, I attached an exact format example of the input I have and output I need for 1800 different rows.
If possible through the same macro, names must be concatenated and all unknowns in the group column removed but this can be done in a formula or find/replace.
I have a spreadsheet example I can include to someone if they'd like. Below is an example from a different forum where a user appears to have the identical issue but never posted their solution. I assume similar logic would apply for other contact information (email, phone)
"NOW:
PRIMARY
1|Business Address | 123 Grove St. | New York | NY | 01011 | USA
2|Home Address | 654 Oak St. | Boston | MA | 32456 | USA
3|Business Address | 81 Main St. | Seattle | WA | 14643 | USA
4|Business Address | 987 West St. | Miami | FL | 69434 | USA
5|Business Address | 534 East St. | Miami | FL | 69432 | USA
6|Business Address | 1 GM. Rd. | Detroit | MI | 85345 | USA
SECONDARY
1| | | | | |
2|Business Address | 945 Smith St.| Boston | MA | 32456 | USA
3|Home Address | 12 Tree Rd. | Tacoma | WA | 14641 | USA
4|
5|
6|Home Address | 97 Key St. | Detroit | MI | 04232 | USA
HOW IT SHOULD LOOK:
BUSINESS
1|Business Address| 123 Grove st. etc.
2|Business Address| 945 Smith St. etc.
3|Business Address| 81 Main St. etc...
4| Business Address| 987 West St. etc.....
HOME
1| | | | | (Blank)
2| Home Address| 654 Oak St. etc.
3| Home Address | 12 Tree Rd. etc.
4| |||||(Blank)
5| (Blank)
6| Home Address| 97 Key St. etc."
Thank you,
Sisu
I am having a lot of difficulty reorganizing an input spreadsheet into an output spreadsheet using only formulas. I attempted to use various IF statements to reorganize this how I wanted but after hours with no avail.
The problem I have is a spreadsheet where contact information and details are copied in from a different sheet and must be reorganized to the correct format conditional on their address, email, and phone type.
For example, the sheet generally has two types of addresses (business and home) each with respective address details (street1, street 2, city, state, phone, country). The output sheet must organize all business addresses in one set of columns and all home addresses in another set of columns without displaying what type it is. Similarly for phone numbers, all business phone numbers must be in one column and all home phone numbers in another without displaying what type they actually are (Note- Some phone numbers are "mobile phone": is it possible to have these to output to home and be overwritten if home already exists?). Then finally again, all "main email"s must be in the work email and all "personal email"s must be grouped in the home email column in the output. If it helps, I attached an exact format example of the input I have and output I need for 1800 different rows.
If possible through the same macro, names must be concatenated and all unknowns in the group column removed but this can be done in a formula or find/replace.
I have a spreadsheet example I can include to someone if they'd like. Below is an example from a different forum where a user appears to have the identical issue but never posted their solution. I assume similar logic would apply for other contact information (email, phone)
"NOW:
PRIMARY
1|Business Address | 123 Grove St. | New York | NY | 01011 | USA
2|Home Address | 654 Oak St. | Boston | MA | 32456 | USA
3|Business Address | 81 Main St. | Seattle | WA | 14643 | USA
4|Business Address | 987 West St. | Miami | FL | 69434 | USA
5|Business Address | 534 East St. | Miami | FL | 69432 | USA
6|Business Address | 1 GM. Rd. | Detroit | MI | 85345 | USA
SECONDARY
1| | | | | |
2|Business Address | 945 Smith St.| Boston | MA | 32456 | USA
3|Home Address | 12 Tree Rd. | Tacoma | WA | 14641 | USA
4|
5|
6|Home Address | 97 Key St. | Detroit | MI | 04232 | USA
HOW IT SHOULD LOOK:
BUSINESS
1|Business Address| 123 Grove st. etc.
2|Business Address| 945 Smith St. etc.
3|Business Address| 81 Main St. etc...
4| Business Address| 987 West St. etc.....
HOME
1| | | | | (Blank)
2| Home Address| 654 Oak St. etc.
3| Home Address | 12 Tree Rd. etc.
4| |||||(Blank)
5| (Blank)
6| Home Address| 97 Key St. etc."
Thank you,
Sisu
Last edited:
