Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

This is a complex question with multiple steps.

I have a client who has information that needs to be imported into Salesforce as a note.

They're relatively open on how this is formatted. As long as the information is there, and accessible to a particular accout, they're happy.

Salesforce import for notes requires that the note be in a column. The problem with this is that there are literally about 3,000 different note rows in a separate note file from the main excel file. Each of these note rows is organized in the following format:

ABC Company
Called ABC Company
Had a meeting with ABC Company's Dave at lunch on Friday
Did XYZ with ABC Company


DEF Incorporated
Met DEC Executive while golfing
Sold 30 widgets to DEC yesterday

Basically, company name, followed by a variable number of rows relating to that company, followed by two rows of blank space, followed by another company, etc, etc, until the end of the file.

So I need to:

Either create a separate column for each line after the first inside a group, i.e. as follows:

Called ABC Company | Had a meeting with ABC Company's Dave at lunch on Friday | Did XYZ with ABC Company


Met DEC Executive while golfing | Sold 30 widgets to DEC yesterday

And match those with the a column in the first excel file, based on the first line of the group.

Or I have to figure out some other way of doing this which is more efficient.

share|improve this question

3 Answers 3

This is confusing (eg "Each of these note rows is organized in the following format:" yet the example looks columnar and company name missing from second part of last example) so, with guesswork:

  1. Copy data and paste into Word as unformatted.
  2. Select all and replace ^p^p with something distinctive (zzz?)
  3. Replace ^p with ^t.
  4. Replace zzz^t with ^p
  5. Reimport into Excel.
share|improve this answer
1  
Since he wants the data pipe separated for import into sales force he should replace ^p with '|'. EDIT: the more I think about it, I think he is just swapping one problem for another. I'm not sure if Salesforce will process a bulk import file with a variable number of elements per record. He might have to reformat to Company|Note multiple times. Anyway, good answer. –  WombatPM Jun 18 '13 at 16:12
    
@WombatPM TQ. I know nothing about Salesforce so was only aiming as far as the Excel step. Again reluctant to push my luck (where I did not fully understand the question)! Anyway, OP seems not interested even with three answers to choose from. Certainly pipe could replace pilcrow (or even tab?) if that would cut out one step. –  pnuts Jun 18 '13 at 16:40

The trick will be to make sure that the data is really regular. If each record is exactly 4 rows separated by some blank lines then you are golden, if the number of lines varies you may have to tweak the steps below. While I'd normally jump in and write a macro, it is possible to reformat this data using just regular formulas in excel.

Step 1.
Insert a new column to the left of all of your data, and a blank row above your data ( ie your data now starts in B2 and All of column A is blank)

Step 2. Mark the start of each record using a formula. In A2 insert the following formula. Use the fill down function to copy this formula down the entire spreadsheet

=IF(AND(ISBLANK(B1)*NOT(ISBLANK(B2))),"START","")

Your data now looks like this:

START   ABC Company
        Called ABC Company
        Had a meeting with ABC Company's Dave at lunch on Friday
        Did XYZ with ABC Company


START   DEF Incorporated
        Met DEC Executive while golfing
        Sold 30 widgets to DEC yesterday

Step 3. Now we can write a formula to combine everything in column C (or further to the right)

=IF(A2="START",CONCATENATE(B2,"|",B3,"|",B4,"|",B5),"")

Your Column C will then end up looking like:

ABC Company|Called ABC Company|Had a meeting with ABC Company's Dave at lunch on Friday|Did XYZ with ABC Company





DEF Incorporated|Met DEC Executive while golfing|Sold 30 widgets to DEC yesterday|

Step 4. Select All, Copy, Paste Special - values only

Step 5. Sort column C so that the blank rows drop to the bottom, copy and paste into a text file and you're good to go for loading into Salesforce.

share|improve this answer
1  
Are you suggesting manual tweaking, because OP does state "company name, followed by a variable number of rows relating to that company"? –  pnuts Jun 13 '13 at 14:01

And there's always the macro option. The following assumes 1) that your data is all in column 1 and 2) that each of your companies is separated by a blank row. Feel free to comment with adjustments that need made.

You may wish to create a copy of your worksheet before running this.

Sub Repackage()

    Dim lRow As Long
    Dim lDropRow As Long
    Const iCol = 1

    lDropRow = 1
    lRow = 2
    Do

        ' Check for blank entry - indicates new company
        If Cells(lRow, iCol) = "" Then

            lDropRow = lRow
            Rows(lRow).Delete

            lRow = lRow + 1

        Else

            ' Note confirmed - aggregate notes into single line
            Do
                Cells(lDropRow, 256).End(xlToLeft).Offset(0, 1) = Trim(Cells(lRow, iCol))
                Rows(lRow).Delete
            Loop Until Cells(lRow, iCol) = ""

        End If

    Loop Until lRow > Cells(65536, iCol).End(xlUp).Row

End Sub
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.