Solved

Move to the right two cells based on previous cell

Posted on 2013-06-18
8
295 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Macro to change number to Month in Excel? 10 45
combine fist two words in a cell 2 24
Clear a Text Box 7 24
vba copy paste previous line 3 15
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

860 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