Excel - What am I looking at?

[msgBldr]

I'm impressed that BC3 can compare Excel files at all. But it does and thank you for that.

I'm not clear what I'm looking at though. There are "tabs" horizontally across the top, are these the same tabs as the spreadsheet? One has a key, several have grey boxes, and others have red boxes (actually square dots).

Vertically, there are numbers down the left side, like row numbers but out of order.

I searched help and this forum for "excel" but came up with nothing very helpful. I can get what I need without understanding what it all means but I'd rather know, could be useful.

tia.... Bob

[Aaron]

Hello,

Our Data Compare assumes that the Excel file has a Key column (which defaults as the left most column). A Key column needs to be a unique identifier that all of the rows then align on, like an Employee ID.

You can setup a combination of columns to be the "Key column" if more than one can make this unique identifier (First Name, Last Name).

You can turn off the sorting in the Session menu -> Session Settings, Columns tab, enable Unsorted Alignment. But it will still only align based on the Key.

You can install our Excel rule that opens your files in the Text Compare as comma separated values here if you do not have a Key column:
http://www.scootersoftware.com/downl...kb_moreformats

Does that help clear things up? Let us know if you have any questions.

[msgBldr]

So far, so good

To see the spreadsheets that I am working with go here.
I understand a little bit more:

• Only compares one sheet of the workbook
• Sheet it compares is the highlighted sheet of the left workbook
• Can select more than one key col with right-click

I'm still not clear what some things the red square dot next to the column numbers means.

A few other questions:

• There are 31 rows in the compare. How is this determined?
• Is it possible to ask BC to simply compare rows 1 thru whatever without sorting and without a key field?
• Is it possible to change the sheet being compared during the compare? That is, without closing the compare, opening the spreadsheet, selecting another sheet, and restarting the compare?

Thanks for your help and for a great product.
...Bob

[Aaron]

Hello,

The Data Compare only compares a single worksheet at a time. The Text Compare rule can display all of the worksheets. You can alter the Data Compare rule to use the Text Compare rule's external conversion to display all of the sheets, but this will put them all in a single sheet and sort all the rows together; it will also introduce 1 blank line per extra sheet (between sheets if unsorted).

The number of rows is determined by aligning the keys (matching rows) and any orphan rows (rows that do not match to the other side). The red square indicates there is a difference present in that column.

The Data Compare requires a key column. You can turn off sorting in the Session Settings dialog as I detailed above, but it will still require equal and identical keys to align the rows. The Text Compare does not use a key column, and relies on a text based algorithm to align all of the text. I would suggest installing this additional file format and trying it out.

[msgBldr]

OK. Thanks for your patience. I think I'm getting it. Sorry I missed your instruction about turning off sorting. Duh. Amazing what happens when you follow directions.

I think part of MY problem is that I only expect simple things. BC does a lot of very useful and complext things, I have to learn more about that.

Thanks again... Bob

[Aaron]

No problem, Bob.

Let us know if you have any other questions about the comparison. I still recommend trying the Text Compare rule, too. You may find one or the other more useful. You can pick which is used automatically by making it the topmost rule in the Tools menu -> File Formats dialog. The lower file format can be picked manually by opening that type of comparison (Data or Text).