Free Microsoft Excel 2013 Quick Reference

Excel to text compare Results

Dear Friends

This is my first post in this forum and I hope to have good knowledge from you guys.

I have a real world problem before me in VBA. I have a couple of workbooks having some worksheets in them. I want to extract these worksheets from them, convert them into text files, compare them in a software UltraCompare and get the comparison text files, convert these comparison files back into excel worksheets and finally want to merge these worksheets in a resulting workbook.

Half of the things I have done from my basic knowledge and use of internet, and my code is as per below:

Option Explicit
Sub Compare_Workbooks()

Dim Wb1 As Workbook
Dim Wb2 As Workbook

Dim Wb1FullPathName As String
Dim Wb2FullPathName As String

Wb1FullPathName = "C:compareworkbook1.xls"
Wb2FullPathName = "C:compareworkbook2.xls"

Set Wb1 = Application.Workbooks.Open(Wb1FullPathName)
Set Wb2 = Application.Workbooks.Open(Wb2FullPathName)

Dim i As Long
For i = 1 To Wbook1.Worksheets.Count
If Wb1.Worksheets(i).Name <> Wb2.Worksheets(i).Name Then
Exit Sub
End If
Next

Dim WS1 As Worksheet
For Each WS1 In Wb1.Sheets
WS1.SaveAs Filename:="C:TextFiles1" & WS1.Name, FileFormat:=xlTextMSDOS
Next WS1
Wb1.Close

Dim WS2 As Worksheet
For Each WS2 In Wb2.Sheets
WS2.SaveAs Filename:="C:TextFiles2" & WS2.Name, FileFormat:=xlTextMSDOS
Next WS2
Wb2.Close

' ***** PROBLEM STARTS FROM HERE *****
Dim n As String
For Each n In GetPath("", "C:TextFiles1")
Shell ("C:Program FilesIDM Computer SolutionsUltraCompare>uc.exe -t -o C:TextFiles1n.txt C:TextFiles2n.txt C:TextResultsn.txt")
Next n

'NEED CODE TO CONVER TEXTRESULTS BACK INTO WORKSHEETS & MERGE THEM IN A WORKBOOK

End Sub

My problem starts from the commment ' ***** PROBLEM STARTS FROM HERE ***** in above code, here how can i open each text files from two locations to compare them and then to get comparison results in TextResults folder.

And then I need to conver the text files from TextResults folder into excel worksheets and then want to merge these worksheets in a workbook.

I hope this post gets some replies and thank you for the same in advance.

Best Regards
Vishalkumar

I have some excel sheets that were created by several different people and using different formating.

The date column is partially formated. When I convert it to text most of the column is okay but some dates are not. It is converting to 5 numbers.

There is a custom date format on some -

mmmm d","yyyy

But when I use the formula below is won't work and says error.

=Text(A1,mmmm d","yyyy)

I had copied the form from an old post for converting date to a day
=Text(A1,DDDD") so I have obviously done it wrong.

If that is not possible is there a formula for comparing 2 columns and then only show the formated dates so you could manually change them

I would appreciate help with this as we have numerous excel sheets we have done to list information re indexes for cemetery records, birth records, etc. for a Research Society and the sheets must be in a text format to import them into the database program we are using.

Thank you

When creating an html file using Microsoft Excel 2002 (10.6501.6735) SP3
to compare the first ever printed English books side by side as posted at:
http://pcministry.com/PentJona/index.htm
Then editing the HTML files manually with another text editor in order to
include hypertext for going directly to a passage:
1:1
to
1:1
And later trying to update the file in Excel again, after saving it, found
out that Excel removes the hypertext: 1:1
Thus all the hypertext development I had done was lost. Although it was a
good thing I saved a backup copy of another similar work at:
http://faithofgod.net/compare/
That in order to get around this problem (before using excel) had to replace
all

that way Excel would retain these tags after saving the new tables, where I
could revert it back with the text editor.
The reason for doing all this was to go directly to a passage. For example
you could go to Hebrews Chapter 1 verse 2
http://pcministry.com/compare/Hebrews.htm#1:2
or another ensample from the original spelling version,
Matthew chapter 13 verse 52
http://pcministry.com/NTcompare/Matthew.htm#13:52

Is there a feature I'm not aware of, or will there be a patch for Excel to
retain hypertext?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

I am trying to match / compare to separate excel columns. My objective is to
find out the silimarities and diferences within the 2 columns, without
distorting the order of one of the columns. Can this be done ?

selection Choice1 choice2
A1 x x
A2 x
A3 x
A4 x
A5 x x
A6
A7 x
A8 x
A9 x x

The table above is done in excel. How can I output the data in a text
file as
Choice1(A1,A3,A4 etc..)
Choice2(A1, A2, A5 etc.)
I basically want to list all the selection with X's with the
corresponding
choices. The selection may very large as compared to what is
demonstrated
here. is there anyway I can get this done using VSB?

I am using Windows XP, Excel 2003.

--
Exceluser

When creating an html file using Microsoft Excel 2002 (10.6501.6735) SP3
to compare the first ever printed English books side by side as posted at:
http://pcministry.com/PentJona/index.htm
Then editing the HTML files manually with another text editor in order to
include hypertext for going directly to a passage:
1:1</td>
to
1:1<A NAME="1:1"></A></td>
And later trying to update the file in Excel again, after saving it, found
out that Excel removes the hypertext: 1:1<A NAME="1:1"></A>
Thus all the hypertext development I had done was lost. Although it was a
good thing I saved a backup copy of another similar work at:
http://faithofgod.net/compare/
That in order to get around this problem (before using excel) had to replace
all
<A NAME= with <!--A NAME=, and </A> with </A-->
that way Excel would retain these tags after saving the new tables, where I
could revert it back with the text editor.
The reason for doing all this was to go directly to a passage. For example
you could go to Hebrews Chapter 1 verse 2
http://pcministry.com/compare/Hebrews.htm#1:2
or another ensample from the original spelling version,
Matthew chapter 13 verse 52
http://pcministry.com/NTcompare/Matthew.htm#13:52

Is there a feature I'm not aware of, or will there be a patch for Excel to
retain hypertext?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

I am using a Vlookup macro to compare over 4000 cells and when it runs it reformats the entire column from numbers to text.

Is there a way to make excel not do this? My code never references the numbers as text so I can't figure out why it's getting reformatted.

Thank you so much!It is not working as yet, I hope I get it to work
eventually. I keep giving me a 1004 error.

A C Wrote:
> "Exceluser" Exceluser.1r1rhn@news.officefrustration.com wrote in
> message
> news:Exceluser.1r1rhn@news.officefrustration.com...-
>
> Hi A,
>
> Thank you so much for your help, the While statement and empty is
> giving me an error.
> -
>
> Hi
>
> Whats happening here is an issue here with my code rolling into
> multiple
> lines when I pasted it into the posting.
>
> I have attached a txt document where I copied and pasted the code
> straight
> from the VBA module. This should preserve where new lines are
> appearing.
> Try copying from this and see if it works. If not I can send you the
> .xls
> itself if you supply an email address.
>
> Regards
> A
> -
>
>
>
> A C Wrote:-
> "Exceluser" Exceluser.1qzwtn@news.officefrustration.com wrote in
> message
> news:Exceluser.1qzwtn@news.officefrustration.com...-
>
> selection Choice1 choice2
> A1 x x
> A2 x
> A3 x
> A4 x
> A5 x x
> A6
> A7 x
> A8 x
> A9 x x
>
> The table above is done in excel. How can I output the data in a
> text
> file as
> Choice1(A1,A3,A4 etc..)
> Choice2(A1, A2, A5 etc.)
> I basically want to list all the selection with X's with the
> corresponding
> choices. The selection may very large as compared to what is
> demonstrated
> here. is there anyway I can get this done using VSB?
>
> I am using Windows XP, Excel 2003.
>
>
> --
> Exceluser-
>
> Below is a macro which will do what you want.
> Assumptions:
> * There are NO BLANK ROWS and NO BLANK COLUMNS in your table of
> data
> * Every choice column has a header, eg Choice1
> * An empty cell indicates no selection, anything else in the cell
> indicates
> a selection (you used "x" in your example).
> * You have set up 2 ranges:
> Filename - this holds the name of the file you want to write to,
> including
> the path. (or you could hardcode it into the VB code, i have
> supplied
> an
> example in the code itself)
> Selection - the "Selection" fieldname cell
>
> The code also has no error checking, so for example if the filename
> is
> invalid it will crash. You might want to add error checking later.
> There is some sample code in there if you dont want to output an
> empty
> choice, ie there were zero selections in that column of data.
>
> Hope this helps
>
> Regards
> A
>
> Sub Macro1()
>
> 'Open the file for output
> 'Filename = "c:Tempselection.txt"
> Filename = Range("Filename").Value
> filenumber = FreeFile
> Open Filename For Output As #filenumber
>
> Range("Selection").Select
> Range("Selection").Activate
>
> 'Loop over all the choices
> colOffset = 1
> While (ActiveCell.Offset(0, colOffset).Value "")
> 'Start the output string
> outputStr = ActiveCell.Offset(0, colOffset).Value & "(" 'eg Choice1(
> 'Walk down each row and include it in the string if the cell is not
> empty
> rowOffset = 1
> While (ActiveCell.Offset(rowOffset, 0).Value "")
> If (ActiveCell.Offset(rowOffset, colOffset).Value "") Then
> 'This is selected, add it to the string
> outputStr = outputStr & ActiveCell.Offset(rowOffset, 0).Value &
> ","
> End If
> rowOffset = rowOffset + 1
> Wend
> 'Each row checked, close off the string
> If (Right(outputStr, 1) = ",") Then
> outputStr = Left(outputStr, Len(outputStr) - 1)
> End If
> outputStr = outputStr & ")"
> 'Add the output string to the file
> Print #filenumber, outputStr
> 'OR WRITE USING THIS CODE IF YOU DONT WANT TO PRINT OUT AN EMPTY
> SET,
> eg
> Choice1()
> 'If (Right(outputStr, 2) "()") Then
> ' 'Add the output string to the file
> ' Print #filenumber, outputStr
> 'End If
>
> 'Try the next column
> colOffset = colOffset + 1
> Wend
>
> 'Close the file
> Close #filenumber
>
>
> End Sub-
>
>
> --
> Exceluser-

--
Exceluser

Good afternoon,

I'm wondering if there is a way to automatically format a cell category from "Date" to "Text" keeping the date structure mm/dd/yyyy?

I'm automatically converting a txt file to xlsx. Using the "MATCH" formula, I'm trying to link one worksheet to the converted one by matching dates. The problem is the converted file keeps all cell formats as "General".

I'm trying to compare today's date using =Today() to the date in the converted file but the converted file's date is general and mm/dd/yyyy which excel doesn't seem to support. For example, excel knocks a zero off for January 1, 2011 (shows up as 1/1/2011 not 01/01/2011).

If anyone can show me how to convert today's date to a "Text" category keeping the mm/dd/yyyy format, it would be very much appreciated.

Thanks for looking

Hi all,
I think this question really gets at a larger issue but here goes:

I'm doing a conditional format of a cell and I'm just comparing it to the cell next to it, and highlighting the cell if it's different than the one next to it. IE, in A1 the condition is <> A2.

It doesn't work...the issue seems to be this ongoing thing about what's a number and what's not. Cell A1 and A2 are both formatted as Text but *currently* contain only digits, ie, A1 = 1033 and A2 = 1033 but the A1 still gets highlighted.

I tested by changing the condition to <> INT(A2) and it works. Since these cells may contain alpha, I can't use INT() reliabley and I don't want to get into the hassle of using IF(), etc. within the format logic--I guess if I have to then I have to, but I get to a point and think "There's got to be an easier way".

Soooo...how do I get Excel to simply compare the cells as I have explicitly formatted them without writing a hundred lines of code in a formula that simply compares one cell to another? It's as if Excel is looking at numeric data as numeric--but only on one side of the comparison, it's not consistent in it's annoying tendencies.

What is the best way to get around this?
Thanks,
--Jim

Hi,

I would like to make sure that two text entries are similar (but not necessarily EXACTLY the same).

The first few letters of the two entries will always be the same in my case though.

i.e. the 2 bits of data might be Higher and Highest and i want excel to return a true when i ask it if they are the same.

How can i make excel (using vba) check that these two bits of data are 'the same' (even though they arent exact)?

Thanks

AS

Hi!

I've finally managed to get a mailmerge to work, and now I need a way to identify in excel the records that were successfully merged so the next time I run the merge I know which ones were already merged.

I was thinking the easiest way would be to get excel to run a match type macro comparing the text in each cell of a specified named range to any/all text in my document and when there was a match, increase the count in the "Print" column of each matching record in excel by 1.

But I can't seem to come even close to what I'm trying to accomplish. Anyone have any easier ideas how i could accomplish this, or where I'm going wrong in my code ('cause I know I'm WAY off track). I tried to change some elements of the "BCMerge" macro that i got from these forums which pushes named cells in excel to matching named bookmarks in word, but something has gone VERY wrong. Please Help!


	VB:
	
 MarkRecordsMerged() 
     
    Dim oWdApp As Word.Application 
    Dim oWdDoc As Word.Document 
    Dim DocPath As String 
    Dim wb As Excel.Workbook 
    Dim xlValue As Excel.Name 
    Dim ActivityDescription As Range 
     
    Set wb = ActiveWorkbook 
    Set oWdApp = New Word.Application 
    oWdApp.Visible = True 
    DocPath = wb.Path & "Labels1.doc" 
     
    Set oWdDoc = GetObject(DocPath, "Word.Document") 
     
    For Each xlValue In ActivityDescription 
        If oWdDoc.Sentences.Item(xlValue.Name) Then 
            oWdDoc.Sentences(xlValue.Name).Range.Text = Range(xlValue.Value) 
        End If 
    Next xlValue 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello, Im new and Im trying to write some code use VBA in excel to look at cell C2 and if it equal A0 or whatever then it will put in cell D2 = "blah blah"
and then go down to the next cell C3.
My thinking was this

	VB:
	
 Range("C:C").Text 
Case "A0" 
    Range("B1").Value = "not booked" 
Case "A1" 
    Range("B1").Value = "Misplace documents" 
Case "A2" 
    Range("B1").Value = "Closing cancelled" 
Case Else 
    End 
End Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Basically I have a report that in one column codes (A1,A2,A3,B3 etc..) and these codes have a description and I want the VBA code to look at the description Code and then put in a different column the description. but Im having no luck.please help me ASAP!!

I want Excel to compare two columns of numbers. Column R has 1000 entries,
beginning with the number 1 and ending with the number 1000. Column S will
have from approx. two hundred to as many as six or seven hundred entries,
looking something like this: 3, 5, 6, 10, 11, 13, 14, 15, 18 . . . . . . 990, 992,
995, 996, 999, 1000. I want Excel to compare column S with column R and
display the difference in Column T. Column T will therefore look like: 1,2,4,
7, 8, 9, 12, 16, 17, 19 . . . . . 899, 991, 993, 994, 997, 998. I have to do
this multiple times, does Excel have a built-in function(s) that can do this or
do I need to write a macro? The numbers can be formatted as text if neces-
sary. Anyone have an idea?

I'm trying to write a macro for excel that will compare each in each row with each cell in every other row (including "hidden" cells), and color the repeats red (i.e. if row 10 and row 11 are identical in every column, color the entire red for both row 10 AND row 100).
I'd also like for the macro to be able to color both rows pink if there is only 1 difference between the rows, but I didn't have any idea as to how to do that, so I didn't attempt that with my macro.
I've done programming with C++ before, but I'm new to excel macros, so I'm having trouble with my "grammar", and VBA seems to be a bit particular. I'm usuing Office 2003, if it makes any difference.
What I've got so far:


	VB:
	
 RepTest() 
     
    Dim r As Long 
    Dim c As Long 
    r = 7 
    c = 4 
     
    For r = 7 To 300 
         
        Do 
            If Cells(r, c).Value = Cells(r + 1, c).Value Then 
                c = c + 1 = True 
Else: 
                Exit Do 
            End If 
            If c = 63 Then cell.EntireRow.Interior.Color = RGB(255, 0, 0) = True 
        Loop Until c = 63 
    Next 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I keep getting an error in the cell ref within the first If statement; it seems to be telling me I'm misusing the Cells(rowvalue, columnvalue) tool, but I can't seem to figure out what's wrong with the way I'm using it; I'm not getting much help from the VBA "help" function.
Also, if someone is feeling especially helpful:
I'd rather not use "r=7 To 300", but instead use something that would essentially equate to "r=7 To 'the last row that has text in it'". This is because the sheet will continue to grow over time, and I'd like to use something that won't have to be fixed in a few months, but also won't take forever.
I'd like for the macro not to highlight blank rows as "equal"; if this isn't possible, I can just write a second one to un-highlight blank rows afterwards, but it'd be nice to have it all within one piece of code.

Please note: I would rather avoid conditional formatting for two reasons; firstly I need to compare each row to every other row, not only consecutive rows, and secondly I'd like to be able to simply tell this macro to run for whatever data I'm using it for, as opposed to having to change the conditional formatting every time I want to view the data in this way. Also, I'm trying to teach myself to do this, so if you're able to tell me how to fix my code, rather than post a new one, that would be incredibly helpful.
As always, shorter and simpler is sweeter.
Thanks in advance for any help.

Can somebody help me how to get excel to highlight cells automatically that have similar text. The problem is that I want to exlude certain part of the text while comparing. For example, if I have three rows as below, I want the two sampleville's to be highlighted. I do not want "CSA" to be compared and highlighted. Also, I do not want to split the cell to separate out CSA from the name. Any ideas on how to do it?

Sampleville CSA Sampleville Bakers Corner CSA

Thanks for your help.

jig

I have not had to play with VBA code for the best part of 10 years and as you can imagine I am pretty darn rusty. To save me wasting time trying to do something in a stupid way I would appreciate it if anyone can recommend a way to solve the following problem:

I have two excel workbooks, one contains raw manufacturing telemetry data and a second contains report data which is (supposedly) a subset of the first workbook. I have been tasked with comparing the two to find any serious discrepancies.

The raw data is in the form:

, , ,

with a few extra fields. All fields are text apart from the date and the value. The extract from the report contains the same fields and enriched data from other sources.

What I am thinking about doing is writing a macro which will go through each line of the raw data and delete lines where the value = 0, then taking the rows of non zero elements and creating a unique key field using the formula =+A1&B1$C1 etc. I would then repeat the exercise for the second workbook, although there would not be any zero entries to remove, compare the unique key fields and report any discrepancies.

My question is two fold -
1) What would the best way to go about cycling through 4000 lines of workbook A and removing lines with zero values for column F
2) What would be a sensible way of comparing column G in workbooks A and B

Any help would be much appreciated

Steve

Howdy, I'm new, and have searched this fantastical place, but cannot seem to find a solution for my problem (although i did learn that you can link cells to text boxes... super fancy)
Anywhatever,
I've got this beautimous group of charts and text boxes laid out on a 3D background (I know roughly nothing about excel compared to you folks, so this i find exciting). The problem is that i need to be able to move the group into power point and possibly other Word Apps. When i do this however the font in the text boxes goes all Times New Roman on me...
I know i could do a screen shot, but I need to verify that would be my only option.

That and these stinkin' data labels... Gawsh, I spend forfreakinever trying to size charts so that the text doesn't end up looking like:
I Ha
te d
ata l
abel
s
(yes i need them to be able to pull from cells, so manually entering isn't much of an option)
Any help is appreciated, and if another topic does these issues justice, please just point me in the right direction.
Thanks

I'm a new member and an Excel beginner. Using Excel 2003, I have 2 lists of names, 1 in column A and a second in column B. (The lists are of different lengths btw, 374 in A and 361 in B.) I want to compare the list in column B to that in column A and preferably generate a list in column C that lists only the new names only found in column B (or at least find some way to "highlight" or otherwise identify them).

Your help would be GREATLY appreciated.

is there a method that will compare two strings of text and return the most likely similar result? for example in column a, i might have "DALBY OFFICESMART", "Office Smart Franchise" and Rentlo and in Col B "Officesmart Master Account". Can i get excel to recognise the first two belong to the same master account and to bring it back as in a vlookup?