It is a little tricky, but doable. I'm looking into this currently, stand by.
Okay, the idea is this.
You have the following layout:
/| A | B | C | D | F |
-+---------+---------+---------+---------+---------+
1| Acc No. | Data1 | Data1' | Data2 | Data2' |
2| 1 | 10 | 11 | a | b |
3| 2 | 100 | 108 | a | a |
4| 3 | 50 | 55 | f | g |
Make a second Sheet:
/| A | B | C | D |
-+-----+-----+-----------------------------+-----------------------------+
1| A/O | Ref | Data1 | Data2 |
2| A | 2 | =INDIRECT("Sheet1!B" & $B2) | =INDIRECT("Sheet1!D" & $B2) |
3| O | 2 | =INDIRECT("Sheet1!C" & $B3) | =INDIRECT("Sheet1!E" & $B3) |
4| A | 3 | =INDIRECT("Sheet1!B" & $B4) | =INDIRECT("Sheet1!D" & $B4) |
5| O | 3 | =INDIRECT("Sheet1!C" & $B5) | =INDIRECT("Sheet1!E" & $B5) |
Columns "A/0" and "Ref" are manual, in my current model. Probably there is a way to automate them but I wanted to keep it simple. Filling down to cover an arbitrarily long input table in Sheet1 would work.
I also did this with Indirect. My formulas on the second sheet look something like:
=INDIRECT("source!A" & INT(ROW()/2)+1)
You will hard-code the letter to indicate the column source, and then the calculation will automatically choose from the correct row. Should copy down as far as you need.
I've done exactly this in VBA a few years ago and it worked great, i guess you could do the samething using C# with Interops?
I could tell what was added, removed and changed. It create a report in the end which was easy to sort out and filter.
Is there a reason why VBA is not an option? And what other options are available?
you could also cross reference the data using Excel functions, but this can be quite complicated for someone who is not an excel power user. general help in excel formulas
Edit: you will need functions like Find Index Offset VLookup Match, this can all be done by combining them. the only drawback is that there is a limit to the formula length. When this limit is reached split the logic in multiple columns or rows.
Edit: you could intergrate the VBA in a single workbook, and have the template saved. each time they wish to compare they use this template to execute the comparisson. this way no deployment is required. they simply need to copy the file and use it.
Edit: The solution proposed by Tomalak may not work since when records are added or removed, we have no control on where these records will be positioned. you will need to find the row with a key match and work from there.
发布评论
评论（6）
这有点棘手，但可行。 我现在正在研究这个问题，稍等一下。
好吧，想法是这样的。
您有以下布局：
制作第二张表：
/| A | B | C | D | -+-----+-----+-----------------------------+-----------------------------+ 1| A/O | Ref | Data1 | Data2 | 2| A | 2 | =INDIRECT("Sheet1!B" & $B2) | =INDIRECT("Sheet1!D" & $B2) | 3| O | 2 | =INDIRECT("Sheet1!C" & $B3) | =INDIRECT("Sheet1!E" & $B3) | 4| A | 3 | =INDIRECT("Sheet1!B" & $B4) | =INDIRECT("Sheet1!D" & $B4) | 5| O | 3 | =INDIRECT("Sheet1!C" & $B5) | =INDIRECT("Sheet1!E" & $B5) |在我当前的模型中，“A/0”和“Ref”列是手动的。 可能有一种方法可以使它们自动化，但我想保持简单。 向下填充以覆盖 Sheet1 中的任意长的输入表是可行的。
It is a little tricky, but doable. I'm looking into this currently, stand by.
Okay, the idea is this.
You have the following layout:
Make a second Sheet:
/| A | B | C | D | -+-----+-----+-----------------------------+-----------------------------+ 1| A/O | Ref | Data1 | Data2 | 2| A | 2 | =INDIRECT("Sheet1!B" & $B2) | =INDIRECT("Sheet1!D" & $B2) | 3| O | 2 | =INDIRECT("Sheet1!C" & $B3) | =INDIRECT("Sheet1!E" & $B3) | 4| A | 3 | =INDIRECT("Sheet1!B" & $B4) | =INDIRECT("Sheet1!D" & $B4) | 5| O | 3 | =INDIRECT("Sheet1!C" & $B5) | =INDIRECT("Sheet1!E" & $B5) |Columns "A/0" and "Ref" are manual, in my current model. Probably there is a way to automate them but I wanted to keep it simple. Filling down to cover an arbitrarily long input table in Sheet1 would work.
一个简单的解决方法可能是使用条件格式（说明适用于 Office 2007）
应用该规则，所有不匹配的条目将以您选择的样式突出显示。
A simple workaround might be to use Conditional Formatting (directions are for Office 2007):
Apply the rule, and all the entries that don't match will be highlighted in the style you selected.
我也用间接做到了这一点。 我在第二张表上的公式如下所示：
=INDIRECT("source!A" & INT(ROW()/2)+1)
您将对字母进行硬编码以指示列源，然后计算将自动从正确的行中选择。 应该根据需要抄下来。
I also did this with Indirect. My formulas on the second sheet look something like:
=INDIRECT("source!A" & INT(ROW()/2)+1)
You will hard-code the letter to indicate the column source, and then the calculation will automatically choose from the correct row. Should copy down as far as you need.
我认为这只有通过编程才有可能（嘿，这是一个与编程相关的网站）。
当VBA不可行时，是否允许使用VB.Net或C#？
I think it is only possible with programming (hey, this is a programming-related site).
When VBA is not an option, is it allowed to use VB.Net or C#?
条件格式可以完成这两项工作，因为每个单元格最多可以有 3 个条件
您可以（比如说）使用红色粗体文本突出显示任何不同的单元格，使用这样的验证公式
eg =(DataA!C8 <> DataB!C8)
您可以使用这样的公式（如单元格 C8 中使用的）对交替行进行着色
eg =(MOD(CELL("Row",C8),2)=0)
将对偶数行进行阴影处理。 当然，要对奇数行进行阴影处理，请在公式末尾使用 =1 而不是 =0
Conditional formatting can do both jobs, because you can have up to 3 conditions per cell
you can (say) use red bold text to highlight any cells which are different, using a validation formula like this
eg =(DataA!C8 <> DataB!C8)
you can shade alternate rows using a formula like this (as used in cell C8)
eg =(MOD(CELL("Row",C8),2)=0)
which will shade even rows. To shade odd rows instead, of course, use =1 at the end of the formula instead of =0
几年前我已经在 VBA 中完成了这个工作，并且效果很好，我想您可以使用带有 Interops 的 C# 来做同样的事情？
我可以分辨出添加了哪些内容、删除了哪些内容以及更改了哪些内容。 它最终创建一个易于整理和过滤的报告。
有没有理由不选择 VBA？
还有哪些其他选择？
您还可以使用 Excel 函数交叉引用数据，但这对于非 Excel 高级用户来说可能相当复杂。 Excel 公式中的一般帮助
编辑： 您将需要“查找”等功能Index Offset VLookup Match，这都可以通过组合它们来完成。 唯一的缺点是公式长度有限制。 当达到此限制时，将逻辑拆分为多列或多行。
编辑：您可以将 VBA 集成到单个工作簿中，并保存模板。 每次他们想要比较时，他们都会使用此模板来执行比较。 这样就不需要部署。 他们只需复制该文件并使用它即可。
编辑： Tomalak 提出的解决方案可能不起作用，因为添加或删除记录时，我们无法控制这些记录的位置。 您需要找到具有关键匹配的行并从那里开始工作。
I've done exactly this in VBA a few years ago and it worked great, i guess you could do the samething using C# with Interops?
I could tell what was added, removed and changed. It create a report in the end which was easy to sort out and filter.
Is there a reason why VBA is not an option?
And what other options are available?
you could also cross reference the data using Excel functions, but this can be quite complicated for someone who is not an excel power user. general help in excel formulas
Edit: you will need functions like Find Index Offset VLookup Match, this can all be done by combining them. the only drawback is that there is a limit to the formula length. When this limit is reached split the logic in multiple columns or rows.
Edit: you could intergrate the VBA in a single workbook, and have the template saved. each time they wish to compare they use this template to execute the comparisson. this way no deployment is required. they simply need to copy the file and use it.
Edit: The solution proposed by Tomalak may not work since when records are added or removed, we have no control on where these records will be positioned. you will need to find the row with a key match and work from there.