vba 如果另一个单元格匹配时单元格为空，如何在行之间复制excel中的单元格数据

How to duplicate cell data in excel between rows if the cells are empty when another cell matches

提问by SwiftD

I know the title is really long winded, I will try to explain

我知道标题真的很啰嗦，我会尽量解释

I am trying to get Magento products into Zen Cart (background only - shouldn't be relevant).

我正在尝试将 Magento 产品放入 Zen Cart（仅限背景 - 不应该相关）。

I have an excel 2010 xls spreadsheet and in it I have a load of product data: skus, prices, categories, etc... you get the idea. I have each of the products along with their data listed once, each with a unique SKU field.

我有一个 excel 2010 xls 电子表格，其中有大量产品数据：skus、价格、类别等......你明白了。我将每个产品及其数据列出一次，每个产品都有一个唯一的 SKU 字段。

But... Some of these products are in more than one category and the way I am importing, each product needs to be listed multiple times, once for each category.

但是...其中一些产品属于多个类别，而我导入的方式是，每个产品都需要多次列出，每个类别一次。

So, I have done an sql dump which contains two columns; sku and category. I have pasted these values into excel as new rows, so now I have a situation like this (with a lot more entries):

所以，我做了一个包含两列的 sql 转储；sku 和类别。我已将这些值作为新行粘贴到 excel 中，所以现在我遇到了这样的情况（有更多条目）：

As you can see for each sku (left highlight) there is one entry containing all the data apart from the category (right highlight) and then there is an additional entry for each category it belongs to which contains only the category and sku but none of the other data.

正如您所看到的，对于每个 sku（左突出显示），有一个条目包含除类别（右突出显示）之外的所有数据，然后它所属的每个类别都有一个附加条目，其中仅包含类别和 sku，但不包含任何一个其他数据。

So, what I need to do is some how copy all the data (apart from the categories column) across all cells with the same sku. Does anyone have any idea how I can achieve this without hitting Ctrl+V several hundred times. I realise VBA can probably handle this pretty easily, but I dont have a clue on that front.

所以，我需要做的是如何在具有相同 sku 的所有单元格中复制所有数据（除了类别列）。有没有人知道如何在不按 Ctrl+V 数百次的情况下实现这一目标。我意识到 VBA 可能可以很容易地处理这个问题，但我对此一无所知。

Any help greatly appreciated

非常感谢任何帮助

回答by Scott

I hope you're willing to use a second sheet, and to reference the data rather than actually copying.

我希望您愿意使用第二张纸，并参考数据而不是实际复制。

On Sheet2, set A1to =IF(Sheet1!A1="", "", Sheet1!A1).? Drag (extend/fill) this down to A500(as much data as you have on Sheet1, or further, to allow for growth).? Also drag A1over to AA1and then drag that down to AA500.

在 Sheet2 上，设置A1为=IF(Sheet1!A1="", "", Sheet1!A1).? 将其拖（扩展/填充）到A500（与 Sheet1 上的数据一样多，或进一步，以允许增长）。？也拖A1过来AA1，然后拖动到AA500。

Then set B2to =IF($AA2="", Sheet1!B2, B1), drag it to Z2, and drag B2:Z2down to B500:Z500.

然后设置B2为=IF($AA2="", Sheet1!B2, B1)，将其Z2拖至 ，然后B2:Z2向下拖至B500:Z500。

P.S. If any of your data (columns) are dates, you will probably need to explicitly format them as dates on?Sheet2.? Ditto for any other values that are formatted any non-default way (e.g., Currency or Percentage).? It may be necessary to do this onlyto cells that have values in them (and not blank cells).

PS 如果您的任何数据（列）是日期，您可能需要将它们显式格式化为?Sheet2.? 上的日期。以任何非默认方式（例如，货币或百分比）格式化的任何其他值也是如此。可能有必要做这个只是给那些在他们（而不是空白单元格）值的单元格。

回答by stenci

You don't need VBA, unless you need to do this many times.
Assuming the sheet with your data is called Sourceand the sheet with the result is called Dest, you can get what you want following these steps on the sheet Dest:

您不需要 VBA，除非您需要多次这样做。
假设您的数据工作Source表被调用Dest，结果工作表被调用，您可以按照工作表上的以下步骤获得您想要的内容Dest：

• On A1 type =Source!A1
• On A2 type =IF(ISBLANK(Source!A2),A1,Source!A2)
• Select the range A1:XX1 (where XX is the last column of the sheet Source)
• Press Ctrl+R (to copy the first cell to the right)
• Select the range A2:XX## (where ## is the last row of the sheet Source)
• Press Ctrl+R and Ctrl+D (to copy to the right and down)

• A1型 =Source!A1
• 在 A2 类型上 =IF(ISBLANK(Source!A2),A1,Source!A2)
• 选择范围 A1:XX1（其中 XX 是工作表 Source 的最后一列）
• 按 Ctrl+R（将第一个单元格复制到右侧）
• 选择范围 A2:XX##（其中 ## 是工作表 Source 的最后一行）
• 按 Ctrl+R 和 Ctrl+D（向右和向下复制）

Here is an explanation of what's going on:
The first row is copied from the Source as it is.
Each cell of the second row is copied from Source only if that cell is empty, otherwise the cell above is copied.

下面是对正在发生的事情的解释：
第一行是从 Source 原样复制的。
仅当该单元格为空时，才从 Source 复制第二行的每个单元格，否则复制上面的单元格。

回答by Peter Albert

Here's the quick and dirty solution:

这是快速而肮脏的解决方案：

1. Select the columns you need to be filled
2. Press Goto Ctrl-G
3. Special `Alt-S'
4. Blanks (Alt-K, Enter)
5. This should select all blank cells. Now type =B2(assuming you're in B3, i.e. use the cell above the active cell) Important: Press Ctrl-Enterinstead of Enter to enter the formula.

1. 选择您需要填写的列
2. 按转到 Ctrl-G
3. 特殊的“Alt-S”
4. 空格 ( Alt-K, Enter)
5. 这应该选择所有空白单元格。现在输入 =B2（假设您在 B3 中，即使用活动单元格上方的单元格） 重要提示：按Ctrl-Enter而不是 Enter 键输入公式。

Done!

完毕！

回答by SkiingSoccerCollie

On a new Worksheet get the numbers 1-26 running across the top in Row1 by typing 1 in cell A1, 2 in cell A2, and then selecting those two cells and filling through Z.

在新的工作表上，通过在单元格 A1 中键​​入 1，在单元格 A2 中键入 2，然后选择这两个单元格并通过 Z 填充，使数字 1-26 在 Row1 的顶部运行。

Now in A2 type the following formula =VLOOKUP(Sheet1!$A1,Sheet1!$A:$AA,VALUE(A$1),FALSE)Fill this formula in the Range A2:Z## (where ## represents your last row of data). Then copy Row1 from Sheet1 to Row1 on Sheet2.

现在在 A2 中键入以下公式=VLOOKUP(Sheet1!$A1,Sheet1!$A:$AA,VALUE(A$1),FALSE)在范围 A2:Z## 中填充此公式（其中 ## 代表您的最后一行数据）。然后将 Row1 从 Sheet1 复制到 Sheet2 上的 Row1。

This formula will copy the rows of data straight down into the empty rows below them (assuming the sku only changes where there is a row of new information.

这个公式会将数据行直接复制到它们下面的空行中（假设 sku 只在有一行新信息的地方发生变化。

This will then all be active formulas, so I would recommend selecting all on Sheet2 and right-clicking in cell A1, and paste special by value.

这将是所有活动公式，因此我建议在 Sheet2 上选择所有内容并在单元格 A1 中右键单击，然后按值粘贴特殊内容。