Solved

Move to the right two cells based on previous cell

Posted on 2013-06-18
8
293 Views
Last Modified: 2013-06-19
Hello all. I need to format an address list in a jiffy.

The data is in EXCEL. But will be incorporated into a WORD document.

The final text will be like this:

Argentina
Argencert S.A. (ARG)
Scope:     Crop, livestock, wild crop, handling
Address: Bernardo de Irigoyen 972, 4 "B"
               Ciudad Autonoma de Buenos Aires
               Argentina
Contact:  Laura Cecilia Montenegro
Phone:    54 11 4363 0033
Email:     info@argencert.com.ar
Website: http://www.argencert.com.ar

The problem is that the second and third line of the address in the column with the "Address:"

How can I tell EXCEL to move the second and third line of the address over one cell to the right. So that the "content" is in the second column?

Many thanks!

Fernando
organic-sample.xlsx
0
Comment
Question by:model_un
  • 5
  • 3
8 Comments
 
LVL 10

Expert Comment

by:mark_harris231
ID: 39257605
Fernando - attached is one example that does some IF/AND comparisons in Column C.  The formula is copied down the sheet.  Once complete, you can use Copy/Paste Special...Values to move Column C in to Column B.

(NOTE: There is an inconsistency in the data format on row 11 of your sample.  In all other instances, there is a blank line between the Website: and the name of the next business.  My formula is dependent on this blank line.  If a line is added before Food Safety S.A. (FS), my formula works.  Was this an entry error or is it possible that this line may be missing in the full data set?)
Copy-of-organic-sample.xlsx
0
 

Author Comment

by:model_un
ID: 39257686
Thank you Mark.

So... as to the inconsistency.

The problem is that for SOME countries you have multiple entries. In those cases you DO NOT HAVE spaces.

What would be the easiest solution? What if I first go in and add a "filler" text. Something that I can easly remove later???

FF
0
 

Author Comment

by:model_un
ID: 39257718
Sorry. Mark. Spoke too soon!!! The spaces are in fact between ALL entries. The missing one in 11 is human error.

But I can fix that with the  "filler text".

The larger issue... is how to do I now get a column that is ONLY descriptives? (scope, contact, email, etc.)
0
 
LVL 10

Accepted Solution

by:
mark_harris231 earned 500 total points
ID: 39257826
See Sheet2.  I used Copy/Paste Special...Values from Columns C, D & E on Sheet1.  I wasn't sure if you wanted the business names to stand apart from the labels.  If not, you could add another column and use CONCATENATE(A1,B1) copied down.  This will merge columns A&B into a single column.
Copy-of-organic-sample.xlsx
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Closing Comment

by:model_un
ID: 39259160
Perfect! I can definately take this and run with it!

Million thanks! I was on the right track with the "IF" function, but could take the next step in cross referencing with the blank cell.

Thanks again,

Fernando
0
 

Author Comment

by:model_un
ID: 39259302
Mark - question... do I need to do the functions in a particular order?

When I add the additional information that needs to be parsed out and then copy down the three functions, I do not get the parsing that occurs in the sample text. I get the placement of a "0" and no movement of the company and country name.

My thought was to add the additional information to your sample and copy down.

There seems to be some hidden number formating that is creating the "0" in blanks cells. When I copy over your "blank" cells the formulas seem to work.

See attached.

Thanks again!

Fernando
Copy-of-organic-sample2.xlsx
0
 

Author Comment

by:model_un
ID: 39259550
Resolved. Had to play around, but with some copying and pasting took care of the issue.

Thanks again!

FF
0
 
LVL 10

Expert Comment

by:mark_harris231
ID: 39259867
Happy to help and glad you were able to bridge the gaps.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now