Solved

excel 2007 duplicate data with unc path

Posted on 2010-11-22
10
320 Views
Last Modified: 2012-06-27
Hi

I have 2 columns in excel 2007 with thousands of entries - ColumnA and ColumnB like so:

ColumnA                                       ColumnB
\\unc\path1\path2\word.doc           Word.doc

What I would like to do and am struggling to find an answer is take ColumnB as an input and remove the duplicated word.doc from columnA, so I would be left with the following:

ColumnA                                       ColumnB
\\unc\path1\path2\                         Word.doc

I am unable to use find replace with wildcards using \ as an anchor, as I have multiple in the unc paths..

Any ideas?

regards
Bryan


0
Comment
Question by:BryanOakley
  • 6
  • 4
10 Comments
 
LVL 13

Accepted Solution

by:
gbanik earned 500 total points
ID: 34188750
Place this formula in the Column C ... if the first 2 columns are filled with the above values
=IF(MID(A1,LEN(A1)-LEN(B1)+1,LEN(A1))=B1,MID(A1,1,LEN(A1)-LEN(B1)),A1)
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34188767
Why substitute should not be used directly is because there could be the text "Word.doc" within the UNC path and just not at the end.
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34188824
In order to eliminate everthing after that last "\"... use this formula
=MID(A1,1,FIND("|",SUBSTITUTE(A1,"\","|",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34188859
Use this formula
=MID(A1,1,FIND("|",SUBSTITUTE(A1,"\","|",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))
in conjunction to the text comparison in the first formula to give best results (ie eliminate the last part only if the last part of the text matches the file name).
=IF(MID(A1,LEN(A1)-LEN(B1)+1,LEN(A1))=B1,MID(A1,1,FIND("|",SUBSTITUTE(A1,"\","|",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))),A1)
0
 

Author Comment

by:BryanOakley
ID: 34189180
hi gbanik

thanks for the reply, much appreciated.
do I highlight the whole column and add the formula?

will this then give me the following?

ColumnA                               ColumnB
\\unc\path1\path2\                 Word.doc
\\unc\path1\path2\                 Word2.doc
\\unc\path1\path2\                 other.html

etc


0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:BryanOakley
ID: 34189393
also the ColumnA unc paths are different lengths
0
 

Author Comment

by:BryanOakley
ID: 34189470
hi

ok, I have it working on 1 cell - I have about 21000 rows - how do I apply to all in the columns??
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34189776
Couple of ways..
1. Just double click the right hand corner of the C1 (where the formula is)
2. Copy cell C1... select the complete range from C2 to the last cell (say C21000 if 21000 is the last row) .. and paste
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34189783
.....le click the right hand "bottom" corner ....
0
 

Author Closing Comment

by:BryanOakley
ID: 34189788
brilliant - worked for me - I just dragged throughout the column and hey presto..!

Marvellous, thanks so much for your time.

regards
Bryan
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
My experience with Windows 10 over a one year period and suggestions for smooth operation
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

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

24 Experts available now in Live!

Get 1:1 Help Now