Conditional Output to Pre-Formatted Worksheet

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 it seems that VBA will need to be used because formulas cannot output to different cells even with a nested loop. But I am unfamiliar with VBA and programming in general...

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. In any event, thank you for continuing to make excelforum.com a valuable resource. I've learned a lot reading the past few hours and hope to one day help others here where I can too.

Thank you,
Sisu

Some Pseudocode:

I am not certain how to perform this or if this would make sense...

Addresses

IF Sheet1.J2="Business Address"
then insert K2:P2 in Sheet2.H2:M2

OR

IF Sheet1.J2="HomeAddress"
then insert R2:W2 in Sheet2.O2:T2

AND

IF Sheet1.Q2="Business Address"
then insert K2:P2 in Sheet2.H2:M2

OR

IF Sheet1.Q2="HomeAddress"
then insert R2:W2 in Sheet2.O2:T2

ELSE

IF Sheet1.Q2="" AND Sheet1.J2=""
then Sheet2.O2:T2, Sheet2.H2:M2=""

Then I assume there would be a similar operation for phone and addresses except changing range and criteria to repeat down the list.

Thank you,
Sisu

HI sisu and welcome to the forum,

I don't recognize any rules that might apply on the Input Sheet to figure out where to place things on the Output sheet.

Concatenating two cells is no problem but figureing if an email address is a home or business is a problem. Also, on the input you have home and business addresses in the same column. What rules do you use to decide if it is home or business? Are the words "home" or "business" in the cell to help decide?

OK - after further study it looks like you have column J, Q, X, Z and AB which are the keys.

Find the attached where I've started a few formulas that should help you get started. They look like this

Please Login or Register  to view this content.

You should rename your worksheets to make the above simpler.

Study my formulas and it should allow you to complete the missing formulas and your transformation.

BTW - VBA was not needed to do this.

Hi Marvin,

Thank you for your response! I studied your formula carefully and it's similar to what I had originally before posting:

Please Login or Register  to view this content.

Perhaps I should explain better or I am not fully understanding the logic, but it appears that both the above (mine) and below (yours)

Please Login or Register  to view this content.

do not organize the new output in the columns WorkAddress1, WorkAddress2... based on the value of the type column being Business Address nor in HomeAddress1, HomeAddress2 based on the value of the type column being Home Address. As it currently is, all outputs are "transferred" without respect to where they are being tranferred to. Unless of course I am missing something...

This user on a different forum appeared to have the same problem (perhaps he's also using the same software I'm using with this ) and made very rough tables showing how the output address in the columns should correspond to their types:

Please Login or Register  to view this content.

In this case it also looks like he has a secondary set of addresses he's trying to compare to the first and determine the output based on type. I am not so certain, but I believe this is the reason VBA is needed as it can compare more than one cell i.e. work v. home at once.


Thank You,
Sisu

Hi,

I stand corrected - which means I was WRONG on my first attempt.
Maybe I shouldn't be answering these questions until after my second cup of coffee?

Thanks for not blindly believing me.

Try this in H2
=IF(In!$J2="Business Address",In!K2,IF(In!$Q2="Home Address",In!R2,""))

See the attached sheet with more effort (and I hope correctness).

The point of focus resides with the home addresses from the input (e.g. 81 Via Lago & 84 Surfer's Paradise) still remaining in the WorkAdd1 column (H) in the output sheet when they should be in HomeAddress column (O) in the output sheet instead when using the formula.

Look at my 7:26 am example attach.

I renamed the sheets to In and Out so they were easier to read. I corrected my earlier mistake.
See if this now works for you.

Excellent! This is progress, thank you

If anyone asks how I did it I can tell them "a little martian helped me" and point them here

EDIT: I took a quick look anyway; it doesn't look like the "out" sheet with both addresses was constructed with the formula. Is there a way to have both addresses on the sheet in the column conditional by type? Currently it is only one.

Thank you again,
Sisu

Hi sisu,
I didn't do anything to the Out sheet. I only used it to look at. I did all my work on the Example Formula sheet.

Hope you can construct the remaining formulas using my examples.

Hmm, I'll keep working on it and stay open in case anyone else wants to try in the meantime...

Edit: Fixed Here: http://www.vbaexpress.com/forum/showthread.php?t=37040