This page shows examples of how to merge and combine data and tables (lists) using criteria. You can also download workbooks with the examples.
Select the data range that you want to use, and then click Kutools Merge & Split Advanced Combine Rows to enable the Advanced Combine Rows dialog box. In the Advanced Combine Rows dialog box, click the column name that you want to combine other data based on. I want the to-be to have: the ID column, repeating 12 times, every 149 rows; a new column that shows the group number, also repeating 12 times, and 17 columns of data. In the as-is, every column has headers, with every group distinguished by a text value, including a number, i.e., “SYS 01”, “SYS 02″,”SYS 12”, that occurs in.
The examples use arrays, ranges, collections, the worksheet function 'CountIf' and loops.
Hello friends, I would like some help in formatting an excel sheet. I have tried multiple scripts from the site and none of them seem to work. If I missed some I apologize. The worksheet starts in Column A but the unique ID is way over in Column X and the additional attendees are in separate rows in the column next to it, which is column Y.
This example requires some values (text or numbers) in cell A1 and down on sheet 1 and 2. Copy the code by selecting it with the mouse, press CTRL+C to copy and paste into a VBA module with CTRL+V.
You can also download a zip-compressed workbook (Excel 2003) with this and the next example.
The code merges the values from 2 lists to 1 sorted list. Even if there are shared values in the 2 lists, the output list will have no duplicates. If for instance list 1 and 2 are like below, the output list will be like the third:
Here comes the code.
Like the previous macro this one requires a vertical list starting in cell A1 on both sheet 1 and 2. The macro compares the two lists and makes two new: One with shared values (present in both lists) and one with non-shared values. The new lists are inserted in column J and K on sheet 1.
To check if a value from one list is present in the other, we use the spreadsheet function 'CountIf'. It counts the instances of a value in a given range. With short lists speed doesn't matter much, but with longer ones we want to minimize looping.
You can download a zip-compressed workbook (Excel 2003) with this and the previous example.
For the code above to be foolproof it should verify that the two tables do exist, and it should also check if the array has more rows than the spreadsheet. If for instance the two lists share no values, the output list will be as long as the two lists put together.
The last example shows how you can combine rows in 2 tables (in 2 different workbooks) based on criterion and insert the result in a new workbook. In this case a table with contact persons and one with company information are combined, if company names match.
Making your own workbooks to test this example is a bit tedious, so I recommend that you download the zip-compressed example workbooks, if you want to see how it works.
The macros are in the workbook 'persons.xls' and for the stuff to work you must insert the path to 'company-list.xls' on the sheet 'Macro'.
The table with contact persons (in 'persons.xls') has the following fields/columns:
Contacts Company Tel. E-mail
The table with companies has the following fields/columns:
Company Address Postal code City Type Info
To put it short the macro loops the two tables, and if the company name matches, the data for the contact person(s) is appended. The new, combined table is put into a new workbook.
The number of columns in the combined table depends on the number of contact persons for each company.
Important tools for the macro are a collection, dynamic arrays, ranges and the spreadsheet function 'CountIf'. The reason for using arrays (and not just ranges) is speed. Here we go with a copy of the whole VBA module:
That was it. As mentioned I recommend downloading the workbooks if you want to see the macros in action. With VBA you can automate a lot in Excel and as shown above also merging and combining data or tables.
If you want to increment a value or a calculation in Excel with rows and columns as they are copied in other cells, you will need to use the ROW function (not if you have a SEQUENCE function). In this article, we will learn how you can increment any calculations with respect to row or column.
|=Expression + ((ROW()-number of rows above first formula )*[steps])|
Expression: This is the value, reference of expression with which you want to increment. It can be a hardcoded value or any expression that returns a valid output. It should be an absolute expression (in most cases).
Number of rows above the first formula: If you are writing this first formula in B3 then the number of rows above this formula will be 2.
[steps]: This is optional. This is the number of steps you want to jump in the next increment.
The Arithmetic operator between expression and formula can be replaced with other operators to suit the requirements of increment.
So that we are familiar with the generic formula, let's see some examples.
Here, I have to create an auto increment ID formula. I want to write one formula that creates ID as EXC1, EXC2, EXC3 and so on. Since we only need to start the increment by 1 and concatenate it to EXC we don't use any steps. EXC is written in Cell C1, so we will use C1 as the starting expression.
Using the general formula we write the below formula in Cell B4 and copy it down.
We have replaced the + operator with ampersand operator (&) since we wanted to concatenate. And since we are writing the first formula in Cell B4, we subtract 3 from ROW (). The result is here.
You can see that an auto increment ID has been created. Excel has concatenated 1 to EXC and Added 1 to previous value. You will not need to write any new formula for creating an ID. You just need to copy them down.
Example 2: Increment the ID every 2 steps
If you want to increment the ID every 2 steps then you will need to write this formula.
The result is:
Example 3: Add Original Value with Every Increment
In this increment the starting expression is added to every incremented value. Here's how it will be if we want to increment the starting value of 100 as 100, 200, 300, and so on.
C1 contains 100.
How does it work?
The technique is simple. As we know, the ROW function returns the current row number it is written in, if now parameter is supplied. The formula above ROW() returns 4.
Next we subtract 3 from it (since there are 3 rows above the 4th row). It gives us 1. This is important. It should be a hard coded value so that it does not change as we copy the formula below.
Finally the value 1 is multiplied (or any other operation) by the starting expression. As we copy the formula below. ROW() returns 5 but subtracting value stays the same (3) and we get 2. And it continues to be the cell you want.
To add steps, we use simple multiplication.
In the above examples we increment by rows. It will not work if you copy them in the next column of the same row.
In the above formula we used the ROW function. Similarly, we can use the COLUMN function.
Generic Formula to Increment by Columns
|=Expression + ((COLUMN()-number of columns on left of first formula )*[steps])|
Number of columns on the left of the first formula: If you are writing this first formula in B3 then the number of columns on the left of this formula will be 1.
I am not giving any examples as it will be the same as the above examples.
Alternative with SEQUENCE Function
It is a new function only available for EXCEL 365 and 2019 users. It returns an array of sequential numbers. We can use it to increment values sequentially, by rows, columns or both. And yes, you can also include the steps. Using this function you will not need to copy down the formula, as Excel 365 has auto spill functionality.
So, if you want to do the same thing as you did in Example no 3. The SEQUENCE function alternative will be:
It will automatically create 100 increment values in one go without you copying the formula. You can read about the SEQUENCE function here.
So yeah, this is how you can do auto increment in Excel. You can increment the previous cell by adding 1 to it easily now or you add steps to it. I hope this article helped you. If it didn't solve your problem, let me know the difficulty you are facing, in the comments section below. I will be happy to help you. Till then keep Excelling.
How To Get Sequential Row Number in ExcelSometimes we need to get a sequential row number of in table, it can be for a serial number or anything else. In this article, we will learn how to number rows in excel from the start of data.
The SEQUENCE Function in ExcelThe SEQUENCE function of Excel returns a sequence of numeric values. You can also adjust the steps in the sequence along with the rows and columns.
Increment a number in a text string in excelIf you have a large list of items and you need to increase the last number of the text of the old text in excel, you will need help of two functions TEXT and RIGHT.
Highlight Row with Bottom 3 Values with a CriteriaIf you want to highlight row values with bottom 3 values and with criteria then this formula will help you out.
50 Excel Shortcuts to Increase Your Productivity Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
How to use Excel VLOOKUP Function This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
How to use theExcel COUNTIF Function Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel This is another dashboard essential function. This helps you sum up values on specific conditions.