How To Transpose Every N Rows of Data into Muliptle Columns in Excel

This post will guide you how to transpose data from rows to column with a formula in excel using both a VBA code and a formula. How do I transpose every N rows from one column to multiple columns in Excel.

In Excel, Transposing data is a common task, but when you want to transpose every N rows of data into multiple columns, it can be a bit trickier. In this post, we’ll show you how to use a VBA code and a formula to accomplish this task easily.

Assuming that you have a list of data in range A1:A10 in column A, and you want to transpose every 2 rows from column A to Mulitple columns. for example, you want to transpose range A1:A2 to C1:D1, A3:A4 to C2:D2. How to do it.

1. Video: transpose Every N Rows of Data into Multiple Columns

Here’s a video tutorial on how to transpose every N rows of data into multiple columns in Excel using a formula and VBA code.

2. Transpose Every N Rows of Data into Multiple Columns Using Excel Formula

You can also transpose every N rows of data into multiple columns in Excel using a formula based on the INDEX function, the Row function and the Column Function. Like as below:

``=INDEX(\$A:\$A,ROW(A1)*2-2+COLUMN(A1))``

Type this formula into cell C1, and press `Enter` key on your keyboard, and then drag the AutoFill Handle to CEll D1.

Then you need to drag the AutoFill Handle in cell D1 down to other cells until` value 0` is displayed in cells.

3. Transpose Every N Rows of Data into Multiple Columns with VBA Code

You can also use VBA code to transpose every N rows of data into multiple columns in Excel. Here’s a step-by-step guide:

Step1: Open the Excel workbook you want to work with and press “Alt + F11” to open the Visual Basic Editor.

Step2: In the Visual Basic Editor, click on “Insert” from the menu bar and select “Module” to create a new module.

Step3: Copy and paste the following code into the module window.

```Sub TransposeRows_excelhow()
Dim i As Long
Dim j As Long
Dim k As Long
Dim LastRow As Long
Dim NumCols As Long
Dim DataRange As Range
Dim DestRange As Range

' Set the number of rows to transpose into each column
NumCols = Application.InputBox("Enter the number of rows to transpose into each column:")

' Select the range of cells to transpose
On Error Resume Next
Set DataRange = Application.InputBox("Select the range of cells to transpose:", Type:=8)
On Error GoTo 0
If DataRange Is Nothing Then Exit Sub

' Select the destination range for the transposed data
On Error Resume Next
Set DestRange = Application.InputBox("Select the destination range for the transposed data:", Type:=8)
On Error GoTo 0
If DestRange Is Nothing Then Exit Sub

LastRow = DataRange.Rows.Count
k = 0

For i = 1 To LastRow Step NumCols
k = k + 1
For j = 0 To NumCols - 1
DestRange.Offset(j, k - 1).Value = DataRange.Offset(i + j - 1, 0).Value
Next j
Next i
End Sub```

Step4: Press “F5” or click on the “Run” button in the toolbar to run the code.

Step5: Enter the number of rows to transpose into each column, such as: 2,Click OK button.

Step6: Select the range of cells to transpose, such as: selecting range A1:A10. Click OK button.

Step7: Select the destination range for the transposed data, such as: Cell C1. Click on Ok button.

Step8: Once you have entered the required information, the selected range of cells will be transposed into multiple columns according to the number of rows you specified.

Step9: select the transposed data and press Ctrl +C to copy it. Then right click on a black cell and select “Transpose” from the Paste option menu list.

Step10: You can quickly and easily transpose every N rows of data into multiple columns using the TRANSPOSE function in Excel.

4. Conclusion

Transposing every N rows of data into multiple columns in Excel can be done using either a VBA code or a formula. Both the VBA code and the formula are effective methods for transposing every N rows of data into multiple columns in Excel. The best method to use will depend on the specific needs of your project and your level of comfort with coding in VBA.

5. Related Functions

• Excel INDEX function
The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
• Excel ROW function
The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
• Excel COLUMN function
The Excel COLUMN function returns the first column number of the given cell reference.The syntax of the COLUMN function is as below:=COLUMN ([reference])….

Get Address of First Cell in Range

We will learn how to get address of first cell in range in this post. At Instance, we must determine the address of first cell in range to use as the data table’s value. For the DATA table, we have a range of value values in the form of an array that is sent to the formula as input, and therefore the formula returns the first cell value.

We will be using the following functions in this article:

The next sections include information on the functions mentioned before that we will use.

ADDRESS retrieves the address of a cell using the row and column numbers.

Syntax:

`=ADDRESS(row_num, col_num, [abs_num] )`
• `Row_num`: The Row number
• `Col_num`: The Column number
• `[abs num]`: [Optional] integer, one of the following values: 1, 2, 3, or 4. If this parameter is ignored or set to 1, the returned address will be absolute, e.g. \$A\$1.

Now we’ll combine these functions into a formula. We’ll begin with a data table. We need to determine the data table’s initial cell address.

Utilize the following formula:

`=ADDRESS(ROW(table_array), COLUMN(table_array))`

Variables:

• `table array`: array representation of a data table

Examples:

Let us validate this formula by applying it to an example.

Here we have a data table and we need to use the formula to get the first cell address in the same data table.

Use the following Formula:

`= ADDRESS(ROW(A1:E9), COLUMN(A1:E9))`

Note: A1:E9 is array of tables

Let’s See How This Formula Works:

`= COLUMN(A1:E9)`

The COLUMN function returns the value of the first column in the table array.

`= ROW(A1:E9)`

The ROW function returns the index of the first row in the table array.

The ADDRESS function accepts the row number and column value parameters and returns the row’s and column’s absolute references.

The array sent to the function is denoted by cell reference. To get the result, press Enter.

As you can see, the formula works correctly since it delivers the value for the first cell in the data table.

Alternatively, you may use the CELL function:

CELL is an Excel built-in information function. Excel’s Cell function saves all the data in a specified cell and returns the cell’s info type. Using the follow formula:

`=CELL("address", [array_reference])`

or

`=CELL("address",INDEX(A1:E9,1,1))`

Note:

• `[array_reference] `: table array given as reference.

As seen by the above calculation, you may use the first cell value as a reference.

The INDEX function can be used to get cell reference to the first cell in the given range by giving INDEX 1 for row number and 1 for column number.

The ADDRESS function may be used in conjunction with the ROW, COLUMN, and MIN functions to get the address of the first cell in a range. I hope this post on finding the get address of first cell in range in Excel was informative. It’s extremely easy and very helpful for people who use excel regularly.

Related Functions

• Excel INDEX function
The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
• Excel ROW function
The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
• Excel ADDRESS function
The Excel ADDRESS function returns a reference as a text string to a single cell.The syntax of the ADDRESS function is as below:=ADDRESS (row_num, column_num, [abs_num], [a1], [sheet_text])….
• Excel COLUMN function
The Excel COLUMN function returns the first column number of the given cell reference.The syntax of the COLUMN function is as below:=COLUMN ([reference])….
• Excel CELL function
The Excel CELL function returns information about the formatting, location, size, or contents of a cell.The syntax of the CELL function is as below:= CELL (info_type,[reference])…
• Excel MIN function
The Excel MIN function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The MIN function is a build-in function in Microsoft Excel and it is categorized as a Statistical Function.The syntax of the MIN function is as below:= MIN(num1,[num2,…numn])….

Extract Multiple Match Values into Separate Columns

If you have a few values/items in the excel sheet and you are thinking that with the aid of the “VlOOKUP” function you can look for a specific value, extract it and then put the matching item into the separate column in Ms Excel easily, then congratulations, you are thinking right, but here a problem arises that there isn’t any doubt that by this way you can extract one or two matches into the separate column easily but with the aid of this way you cannot extract multiple matches into separate columns and if you would do that by this way then there are 90% chances that you would 100% get tired of it and can’t complete your task at the right time.

But don’t be worry about it because after carefully reading this article extracting multiple matches into the separate columns would become a piece of cake for you.

So let’s dive into the article to take you out of this fix.

General Formula:

For extracting multiple matches(items) into seprate columns you can use the  Array Formula which is based upon INDEX and SMALL, which is stated as follows:

`=IFERROR(INDEX(STU_Range,SMALL(IF(CLASS_Range=\$E2, ROW(STU_Range) -MIN (ROW(STU_Range))+1),COLUMNS(\$E\$2:E2))),"")`

Syntax Explanations:

Before knowing about how to use this formula for getting the work done efficiently, we must understand each syntax which would make it easy for you that how each syntax contributes to extracting multiple matches into the separate columns:

• `IFERROR`: This Function returns a custom result whenever a formula generates an error and returns the expected result when no error is detected.
• `INDEX`: In a range or array, this index function contributes to returning the value at a given position.
• `SMALL`: From the given range of data, this small Function returns the Nth
• `IF`: In Excel, this IF Function contributes to returning two different values, one value for the TRUE result and another for the FALSE result.
• `ROW`: In Excel, this Row function contributes toreturning the row number as a reference.
• `MIN`: From the range of input values, this MIN function contributes to returning the smallest numeric value.
• `Absolute Reference`: The Absolute referenceis nothing but an actual fixed location in a worksheet.
• `COLUMNS`: From a given reference, this Column function contributes to counting the columns.
• `Comma symbol (,)`: This symbol acts as a separator that contributes to separating a list of values.
• `Minus Operator (-)`: This minus symbol contributes to subtracting any two values.
• `Parenthesis ()`: The primary purpose of this parenthesis symbol is to group the various elements.
• `Name`– It represents the input ranges in your worksheet.
• `Plus operator (+)`: This plus symbol adds the values.

Let’s See How This Formula Works:

To use this array formula for getting the work done, you must enter this formula with Control + Shift + Enter. As soon as you would enter this formula into the first cell, you need to drag it down and across to fill in the other cells.

As you can see in the above screenshot, this formula uses two names ranging: “ CLASS_Range ” and “ STU_Range,” where “ STU_Range ” refers to B2:B12 and on the other hand “ CLASS_Range ” refers to A2:A12.

You would definitely wonder that how this formula works to extract multiple matches into columns? So here is the answer. In this formula, we use the Small Function and INDEX function, which work together.

As the SMALL Function (dynamically constructed by IF) is used to obtain row number corresponding to an “nth match,” so after getting the row number from SMALL Function, this would then pass it into the INDEX function, which returns the value at that row, this is also the main motive of this formula.

The snippet “IF(CLASS_Range=\$E2, ROW(STU_Range) -MIN (ROW(STU_Range))+1” tests the named range “ STU_Range ” for the value in E2. If the value is found, then from an array of relative row numbers, it would return a row number, which is created with:

`=ROW(STU_Range) -MIN (ROW(STU_Range))+1`

The output of this formula is :

`{1;2;3;4;5;6;7;8;9;10;11}`

Now the final result is an array that would contain the numbers where there is a match, and FALSE where there is not any match found:

`{1;FALSE;FALSE;4;FALSE;FALSE;7;FALSE;FALSE;10;FALSE}`

Then this array goes into the SMALL Function. By expanding range(Given Below), The k value for SMALL (nth) returns:

`COLUMNS(\$E\$2:E2)`

The SMALL function returns each matching row number, which is then supplied as the row_num to the INDEX function as the array with the range named “ STU_Range.”

Notes:

Now, this question would pop up in your mind that how would it handle the errors? Then whenever the COLUMN would return a value for k that does not exist, the #NUM error would be thrown by the SMALL Function at the next moment. This usually occurs when all the matches have occurred. To tackle the errors, the formula is wrapped up in the Function named “IFERROR,” which would receive the errors and then return an empty string (” “).

Related Functions

• Excel INDEX function
The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
• Excel MATCH  function
The Excel MATCH function search a value in an array and returns the position of that item.The MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….
• Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
• Excel ROW function
The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
• Excel SMALL function
The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …
• Excel MIN function
The Excel MIN function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The MIN function is a build-in function in Microsoft Excel and it is categorized as a Statistical Function.The syntax of the MIN function is as below:= MIN(num1,[num2,…numn])….
• Excel IFERROR function
The Excel IFERROR function returns an alternate value you specify if a formula results in an error, or returns the result of the formula.The syntax of the IFERROR function is as below:= IFERROR (value, value_if_error)….
• Excel COLUMN function
The Excel COLUMN function returns the first column number of the given cell reference.The syntax of the COLUMN function is as below:=COLUMN ([reference])….

How to Count Row That Contain Specific Value in Excel

This post will guide you how to count rows that contain a specific text or number in Excel 2013/2016 or Excel office 365. How do I count the number of rows that contain a particular value while working with your current worksheet in Excel. And you can do this by a array formula based on the SUM function, the MMULT function, the TRANSPOSE function and the COLUMN function in Excel.

General Formula:

The below general formula can help you to count rows that have specific values in Excel. Like this:

`=SUM(--(MMULT(--(criteria), TRANSPOSE(COLUMN(Range)))>0))`

Note: this formula is an array formula and you have to press “`CTRL + SHITF +Enter`” to change it from a normal formula to a array formula.

Count Row That Contain Specific Value

Assuming that you have a data of list in range B2:C6. And you want to know the number of rows that have a particular value 60 in your given range of cells.  And you can refer to the above generic formula to create a new array formula. Like this:

`=SUM(--(MMULT(--(B1:C6=60),TRANSPOSE(COLUMN(B1:C6)))>0))`

Let’s See That How This Formula Works:

`=--(B2:C6=60)`

This formula is a logic criteria, and it is used to generate a TRUE and FALSE array result, and the double negative operator can be used to force the TRUE and FALSE values to 1 and 0 respectively.

`{0,0;1,0;0,1;1,0;0,1;0,0}`

The Column function can be used to get the column number in an array format. And the TRANSPOSE function is used to change the column array format to row array.

Finally, the SUM function will count all those rows that have your specific value in the given range of cells.

Related Functions

• Excel COLUMN function
The Excel COLUMN function returns the first column number of the given cell reference.The syntax of the COLUMN function is as below:=COLUMN ([reference])….
• Excel SUM function
The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…
• Excel TRANSPOSE function
Excel TRANSPOSE formula allows you to rotate (swap) values from rows to columns and vice versa in Excel.The Excel TRANSPOSE Function syntax:=TRANSLATE (range) …

How to Sum Every Nth Column in Excel

Sometimes for data listed in rows, we may want to sum data every Nth column, for example sum data only in ODD column or EVEN column (every 2 column). In our daily life, we may meet many cases like this. It is necessary for us to have the knowledge of sum data by every Nth column in excel. Actually, excel built-in functions SUMPRODUCT, MOD, and COLUMN can help us resolve this issue properly.

This article will show you ‘to sum every Nth column’ based on SUMPRODUCT, MOD and COLUMN functions. MOD function is frequently used in sum every Nth case, COLUMN returns column number for a reference. Thus, SUMPRODUCT function is used for ‘sum’, the combination of MOD and COLUMN works on locate ‘every Nth column’. We will introduce above three functions with simple examples, descriptions, screenshots and explanations in this article, and also show you the usage of them. Finally, we will let you know the formula workflow step by step clearly.

After reading the following article, I’m sure you can have a simple understanding of SUMPRODUCT, MOD and COLUMN functions. Besides, you can learn well on sum data every Nth column/row. I’m sure you can work well with these functions in your daily work in the future.

EXAMPLE:

We prepare three lists and set ‘N’ value in J column. N means to sum data every ‘Nth’ column. For example, for list2, N=2, so we sum data every 2 column, in fact we need to sum data in C3, E3, G3 and I3. As N is not a fixed value, it can be any integer, so we need to take this into consideration when creating a formula.

FORMULA:

To figure out this problem, we can apply a formula based on SUMPRODUCT, MOD and COLUMN functions.

Step 1: In K2 enter the formula =SUMPRODUCT(–(MOD(COLUMN(B2:I2)-COLUMN(B2)+1,J2)=0),B2:I2).

Step 2: Click Enter to get return value. Verify that 140 is calculated correctly.

(In list1, N=1, sum data every 1 column means sum data in all columns, we can also use SUM(B2:I2) to sum total directly.)

Step 3: Copy the formula to K3 and K4. References are automatically updated in the formula.

We can see that total values are calculated correctly.

USAGE OF COLUMN and MOD FUNCTIONS

We have introduced SUMPRODUCT function usage in previous articles. In this formula, the core part is the combination of MOD and COLUMN functions. We will have a brief review about SUMPRODUCT function when analyzing the workflow of each function in the formula.

The Usage of COLUMN Function

For COLUMN function, the syntax as below:

=COLUMN ([reference])

COLUMN function can return the column number for a reference. The reference can be a cell or a range.

Usage 1: For example, when we enter ‘=column(B1)’ in D1, column number 2 is returned as B column is the second column in this worksheet. Thus, we can see that COLUMN function only returns the column number for the reference (B1 for example) and it is not affected by the reference value (15 in B1).

Usage 2: If the reference is a range, COLUMN function will return the column number of the leftmost column when pressing Enter directly after entering the formula. See example below. In this case B3:D6 contains 4 rows and 3 columns, COLUMN function will return the leftmost column (B column in this case), so 2 is returned.

Usage 3: But if the reference is a range, COLUMN function will return a horizontal array contains all column numbers in this range when selecting output part and pressing CTRL+SHIFT+ENTER. See example below. Enter =COLUMN(B3:D6) in E1, then in formula bar press F9, we can get an array {2,3,4}. So in this case, COLUMN function can return an array instead of an integer.

Usage 4: By the way, If the reference is omitted, COLUMN function returns current column number. See example below:

The Usage of MOD Function

For MOD function, the syntax as below:

=MOD(number, divisor)

MOD function can return the remainder of the two arguments ‘number is divided by divisor’. We can directly enter the two arguments number and divisor into formula to get remainder, see example below:

Remainder is 0. 0 is returned as 15 is divisible by 3, remainder is 0.

Remainder is not 0. 3 is returned because when dividing 15 by 4, it doesn’t return a whole number, the remainder is 3.

The two arguments also can be two references. Then MOD function will use the reference to execute formula. See example below:

In our instance =SUMPRODUCT(–(MOD(COLUMN(B3:I3)-COLUMN(B3)+1,J3)=0),B3:I3), ‘Number’ is a formula COLUMN(B3:I3)-COLUMN(B3)+1, ‘Divisor’ is a reference J3.

HOW THIS FORMULA WORKS:

After introducing above basic syntax, arguments, usage of COLUMN and MOD functions, we will analysis the working process of our formula in this article. Firstly, we will introduce each function workflow based on list1 and the formula in K2. In our instance list1, for snippet (MOD(COLUMN(B2:I2)-COLUMN(B2)+1,J2)=0), refer to COLUMN function usage#3, COLUMN(B2:I2) actually returns an array {2,3,4,5,6,7,8,9}, please see screenshot below. (select COLUMN(B2:I2) in formula bar, then press F9, you can see the array.)

As COLUMN(B2)=2, J2=1, so MOD(COLUMN(B2:I2)-COLUMN(B2)+1,J2) equals to MOD({2,3,4,5,6,7,8,9}-{2}+1,1). Please see screenshot below.

Continue to calculate the formula, we can get MOD({1,2,3,4,5,6,7,8},1).

In this formula, array {1,2,3,4,5,6,7,8} is argument ‘Number’, 1 is argument ‘Divisor’, as all numbers in the array are divisible by 1, so MOD({1,2,3,4,5,6,7,8},1)=0 equals to MOD({1,2,3,4,5,6,7,8})=0, compare each value in the array with 0, we can get below result.

Add double negative symbol (–) before TURE (or FALSE) result to convert TRUE to 1 and FALSE to 0, then the array converts to {1,1,1,1,1,1,1,1}, see screenshot below:

In list1, we can see that as value in J2 is 1, so sum data every 1 column is equivalent to sum all data in list B2:I2. But for list2, we need to sum data every second column, so actually we need to sum data in C3,E3,G3 and I3. Similar to list1, firstly we get all returned values of COLUMN function.

Now number argument is {1,2,3,4,5,6,7,8}, divisor is 2.

A number is divided by 2, the remainder is 0 or 1. After calculating MOD({1,2,3,4,5,6,7,8},2), we get an array {1,0,1,0,1,0,1,0}.

After comparing with 0, we can get TRUE or FLASE as result.

Convert TRUE to 1 and FALSE to 0 by adding double negative (–).

Now we come to the outermost function SUMPRODUCT. SUMPRODUCT function syntax has the following arguments:

=SUMPRODUCT(array1, [array2], [array3], …)

For list1, the last step is =SUMPRODUCT({1,1,1,1,1,1,1,1},B2:I2). We can see that there are two arrays, the first array is {1,1,1,1,1,1,1,1}, the second array is the values in B2:I2 {10,15,20,30,5,25,20,15}.

So, SUMPRODUCT({1,1,1,1,1,1,1,1},{10,15,20,30,5,25,20,15}) can be seen as value in one array multiplies by the corresponding value in another array, in this case the final array is equivalent to {1*10, 1*15,1*20,1*30,1*5,1*25,1*20,1*15}={10,15,20,30,5,25,20,15}, then sum data in the array {10,15,20,30,5,25,20,15}, 10+15+20+30+5+25+20+15=140.

For list2, the last step is =SUMPRODUCT({0,1,0,1,0,1,0,1},B3:I3). We can see that there are two arrays, the first array is {0,1,0,1,0,1,0,1}, the second array is the values in B3:I3 {20,10,30,40,25,15,35,45}.

So, for SUMPRODUCT({0,1,0,1,0,1,0,1},{20,10,30,40,25,15,35,45}), multiply value in array1 by the value in array2, so we get {0*20,1*10,0*30,1*40,0*25,1*15,0*35,1*45}={0,10,0,40,0,15,0,45}. SUMPRODUCT({0,10,0,40,0,15,0,45})=10+40+15+45=110.

RESULT:

Let’s check the formula workflow in F3. Actually, the workflow for list3 is the same, only divisor is changed to 3 for MOD part.

Step 1: Execute COLUMN function, convert all reference to real values.

Step 2: Execute MOD function.

Step 3: Compare values with 0 and convert TRUE/FALSE to 1/0.

Step 4: Execute SUMPRODUCT function, to sum data based on two arrays. SUMPRODUCT({0,0,20,0,0,35,0,0}=55. So we get 55 in K4.

SUMMARY:

1. COLUMN function returns an array if COLUMN function is entered as a horizontal array formula.
2. COLUMN function returns the leftmost column if COLUMN function is not entered as a horizontal array formula.
3. MOD function is often used in calculating ‘Nth’ rows/columns cases.
4. To sum ODD columns:

=SUMPRODUCT(–(MOD(COLUMN(list)-COLUMN(first cell)+1,2)=1),list)

1. To sum EVEN columns:

=SUMPRODUCT(–(MOD(COLUMN(list)-COLUMN(first cell)+1,2)=0),list)

Related Functions

• Excel SUMPRODUCT function
The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products. The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
• Excel MOD function
he Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….
• Excel COLUMN function
The Excel COLUMN function returns the first column number of the given cell reference.The syntax of the COLUMN function is as below:=COLUMN ([reference])….

How to Select and Copy Cell Values from Every Nth Column in Excel

This post will guide you how to select all Cell values from every nth column in your worksheet in Excel. How do I select all cells in every nth column using a VBA Macro in Excel. How do I retrive or copy cell values from every nth column using a formula in Excel 2013/2016.

Assuming that you have a list of data with hundreds of columns in a worksheet , and you wish to retrive all cell values from every second or third or nth column, etc. How to do it. You can select all cells from every nth column using a VBA macro quickly, and then press` Ctrl + C` short cuts to copy those selected cells. or You can use a formula to retrive all cell values from every nth column based on the` OFFSET function` and the` COLUMN function`.

Select All Cells from Every Nth Column Using VBA Macro

To select all cells from every Nth column (such as: third column), and you can use an Excel VBA macro to quickly select them with the following steps:

Step1: open your excel workbook and then click on “`Visual Basic`” command under `DEVELOPER` Tab, or just press “`ALT+F11`” shortcut.

Step2: then the “`Visual Basic Editor`” window will appear.

Step3: click “`Insert`” ->”`Module`” to create a new module.

Step4: paste the below VBA code  into the code window. Then clicking “`Save`” button.

```Sub CopyEveryNthColumn()
Dim destRange As Range
Set myRange = Application.Selection
Set myRange = Application.InputBox("Select one Range tha that you want to select All nth columns:", "CopyEveryNthColumn", myRange.Address, Type:=8)
nthNum = Application.InputBox("Please type one Nth number that you want to select: ", "CopyEveryNthColumn", Type:=1)
For i = 1 To myRange.Columns.Count Step nthNum
Set myCell = myRange.Cells(1, i + nthNum - 1)
If destRange Is Nothing Then
Set destRange = myCell
Else
Set destRange = Application.Union(destRange, myCell)
End If
Next
destRange.EntireColumn.Select
End Sub```

Step5: back to the current worksheet, click on `Macros` button under `Code` group. then click `Run` button.

Step6: Select one Range tha that you want to select All nth columns, such as: A1:F3 click on `Ok` button.

Step7:Please type one Nth number that you want to select, such as: number 3. click on `Ok` button.

Step8: you would see that every 3rd column has been selected. and then you can press Ctrl+C keys to copy them.

Retrive All Cell Values from Every Nth Column Using Formula

You can also use an formula to copy or retrive cell values from every nth column(third column). see below:

`=OFFSET(\$A1,0,(COLUMN(A1)*3)-1)`

You need to type this formula into a blank cell (such as: A5)and press Enter key on your keyboard to apply it. then drag the AutoFill Handle from Cell A5 to same row cells unitl number 0 is displayed.

Keeping cells are selected. and drag the AutoFill handle down to other cells unitl number 0 is displayed.

Note: if you want to select or copy cell values from nth column in your worksheet, and you just need to change number 3 as you need in the above formula.

Related Functions

• Excel COLUMN function
The Excel COLUMN function returns the first column number of the given cell reference.The syntax of the COLUMN function is as below:=COLUMN ([reference])….

How To Increment Cell Reference by X Rows in Excel

This post will guide you how to increment cell reference by x Rows when you drag a formula to populate cells in a column in Excel. By default, when you drag the AutoFill Handle down to other cells , the cell reference in the formulas will be increated by one. And if you want to increase cell reference by a given number，how to do it.

Assuming that you have a list of data in range B1:B6, and you have a formula in another cell, and you want to increase cell references by 3 rows when dragging AutoFill handle. type：

`=OFFSET(\$B\$3,(ROW()-1)*3,0)`

Note: Cell \$B\$3 is the first cell tha tyou want to extract in your data.

Dragging this formula down to other cells in that column, you would see that the cell references are increased by 3 rows.

If you want to increase cell reference by 3 columns in a row range, just using the following formula:

`=OFFSET(\$D\$1,0,(COLUMN()-1)*3)`

Type this formula into the first cell in a new row, press Enter key ,and drag the AutoFill Handle to other cells.

Related Functions

• Excel ROW function
The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
• Excel COLUMN function
The Excel COLUMN function returns the first column number of the given cell reference.The syntax of the COLUMN function is as below:=COLUMN ([reference])….

How to Get the Active Cell Address in Excel

This post will guide you how to get the active Cell address with a formula in Excel. How do I return address of active cell with a VBA Macro in Excel.

Get the Active Cell Address with Formula

If you want to return the address of an active cell in your current worksheet, you can use a formula based on the ADDRESS function, the ROW function and the COLUMN Function to achieve the result. The formula is shown as below:

`=ADDRESS(ROW(),COLUMN())`

You just need to select a cell and make it as active cell, then enter this formula into the formula bar, press Enter key on your keyboard. You would notice that the address of active cell is returned.

Get the Active Cell Address with VBA

You can also use an Excel VBA Macro to achieve the same result of returning the active cell address to a given cell. You just need to do the following steps:

#1 right click on the sheet tab in your worksheet, and select View Code from the popup menu list. And the Microsoft Visual Basic for Application window will appear. Or you can directly press Alt+ F11 to launch the VBA window.

#2 type the following code into the code window, then click Save button to save it.

```Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub```

#3 close VBA window and back to your worksheet. You can try to click or select one cell, the cell address of active cell is shown in Cell B2.

Related Functions

• Excel ADDRESS function
The Excel ADDRESS function returns a reference as a text string to a single cell.The syntax of the ADDRESS function is as below:=ADDRESS (row_num, column_num, [abs_num], [a1], [sheet_text])….
• Excel COLUMN function
The Excel COLUMN function returns the first column number of the given cell reference.The syntax of the COLUMN function is as below:=COLUMN ([reference])….
• Excel ROW function
The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….

How to Convert Multiple Rows into a Single Row in Excel

This post will guide you how to convert multiple rows or columns into a single row with a formula in Excel 3013/2016. How do I convert groups of rows to a single row of columns with VBA Macro in Excel.

Convert Multiple Row into Single Row with Formula

Assuming that you have a list of data in range A1:B6, and you want to convert those data into a single row in your worksheet, how to do it. You can use a formula based on the OFFSET function, the ROW function, the FLOOR function and the COLUMN function to achieve the result. Like this:

`=OFFSET(Sheet8!\$A\$1,((ROW()-1)*6)+(FLOOR(COLUMN()-1,2)/2),(COLUMN()-1)-(FLOOR(COLUMN()-1,2)))`

You need to type this formula into the cell A1 in a new worksheet in your current workbook. And then drag the AutoFill handle to right until you get the number 0.

Note: the number 6 is the total number or rows. and the number 2 is the total number of  columns.

Convert Multiple Row into Single Row with VBA Macro

You can also use an Excel VBA Macro to achieve the same result of converting multiple rows or columns into a specified row. Here are the steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

#4 paste the below VBA code  into the code window. Then clicking “Save” button.

```Sub convertMultipleRowsToOneRow()
Set myRange = Application.InputBox("select one range that you want to convert:", "", Type:=8)
Set dRang = Application.InputBox("Select one Cell to place data:", "", Type:=8)
rowNum = myRange.Rows.Count
colNum = myRange.Columns.Count
For i = 1 To rowNum
myRange.Rows(i).Copy dRang
Set dRang = dRang.Offset(0, colNum + 0)
Next
End Sub```

#5 back to the current worksheet, then run the above excel macro. Click Run button.

#6 select one range that you want to convert. Click OK button.

#7 Select one Cell to place data.  Click OK button.

#8 Let’s see the result:

Related Functions

• Excel COLUMN function
The Excel COLUMN function returns the first column number of the given cell reference.The syntax of the COLUMN function is as below:=COLUMN ([reference])….
• Excel ROW function
The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
• Excel FLOOR function
The Excel FLOOR function returns a number rounded down to the nearest multiple of significance. So it will return a rounded number.The syntax of the FLOOR function is as below:= FLOOR (number, significance)…

How to convert Column Letter to Number in Excel

This post will guide you how to convert column letter to number in Excel. How do I convert letter to number with a formula in Excel. How to convert column letter to number with VBA macro in Excel. How to use a User Defined Function to convert letter to number in Excel.

Convert Column Letter to Number with a Formula

If you want to convert a column letter to a regular number, and you can use a formula based on the COLUMN function and the INDIRECT function to achieve the result.

For example, you need to convert a column letter in Cell B1 to number

Just like this:

`=COLUMN(INDIRECT(B1&1))`

Type this formula into a blank cell such as: C1, and press Enter key, and then drag the AutoFill Handle over to other cells to apply this formula.

The INDIRECT function will convert the text into a proper Excel reference and then pass the result to Column function to get the column number for the reference.

Convert Column Letter to Number with VBA Macro

You can also use an Excel VBA Macro to achieve the result of converting column letter into its corresponding numeric value. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

#4 paste the below VBA code into the code window. Then clicking “Save” button.

```Sub ConvertColumnLetterToNumber()
Dim myRng As Range
Dim cNum As Double
Set myRng = Application.Selection
Set myRng = Application.InputBox("select one range that contain column letters", "ConvertColumnLetterToNumber", myRng.Address, Type:=8)

For Each myCell In myRng
cNum = Range(myCell & 1).Column
myCell.Resize(1).Offset(0, 1) = cNum
Next
End Sub```

#5 back to the current worksheet, then run the above excel macro. Click Run button.

#6 select one range that contain column letters, such as B1:B4

#7 let’s see the result:

Convert Column Letter to Number with User Defined Function

Excel does not have a built in formula to convert column letter to a numeric number, but you can write down a User Defined Function to achieve the result. Just do the following steps:

#1 repeat the above step 1-3

#2 paste the below VBA code into the code window. Then clicking “Save” button.

```Function ConvertLetterToNum(ColumnLetter As String) As Double
Dim cNum As Double

'Get Column Number from Alphabet
cNum = Range(ColumnLetter & "1").Column

'Return Column Number
ConvertLetterToNum = cNum
End Function```

#3 back to the current worksheet, then type the following formula in a blank cell. press Enter key.

=ConvertLetterToNum(B1)

#4 select the cell C1, and  drag the AutoFill Handle over to other cells to apply this formula.

Convert Number to Column Letter

If you want to convert column number to an Excel Column letter, you can use another formula based on the SUBSTITUTE function and the ADDRESS function. Just like this:

`=SUBSTITUTE(ADDRESS(1,C1,4),"1","")`

Type this formula into Cell D1, and press Enter key. And then drag the AutoFill handle over to other cells to apply this formula.

Let’s see the last result:

Video: Convert Column Letter to Number

Related Functions

• Excel Substitute function
The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string.The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE  (text, old_text, new_text,[instance_num])….
• Excel ADDRESS function
The Excel ADDRESS function returns a reference as a text string to a single cell.The syntax of the ADDRESS function is as below:=ADDRESS (row_num, column_num, [abs_num], [a1], [sheet_text])….
• Excel INDIRECT function
The Excel INDIRECT function returns the cell reference based on a text string, such as: type the text string “A2” in B1 cell, it just a text string, so you can use INDIRECT function to convert text string as cell reference….
• Excel COLUMN function
The Excel COLUMN function returns the first column number of the given cell reference.The syntax of the COLUMN function is as below:=COLUMN ([reference])….

Sum Every Nth Row or Column

This post will guide you how to sum every nth row or column with a formula in Excel. How do I sum every nth value in a same row or column in Excel. How to sum every nth cell in a column or in a row with an Excel Formula. How to sum every second, third, or nth row or column in a range in Excel.

Sum Every Nth Row

If you want to sum every nth rows in Excel, you can create an Excel Array formula based on the SUM function, the MOD function and the ROW function.
For example, you want to sum every 2 rows in the range B1:B6, you can write down this formula to achieve the result.

`=SUM(B1:B6*(MOD(ROW(B1:B6),2)=0))`

You need to type this formula into a blank cell, and press Ctrl +Shift +Enter shortcuts to change the formula as Array formula.

You can change the number 2 as you need to sum every nth row in a specific range of cells in Excel.

Sum Every Nth Column

If you want to sum every nth column in a column, you can create a formula based on the SUMPRODUCT function, the MOD function, and the Column function.

For example, you want to sum every 3rd column in range B1:G1, you can use the following formula to achieve the result.

`=SUMPRODUCT(--(MOD(COLUMN(B1:G1)-COLUMN(B1)+1,3)=0),B1:G1)`

Related Functions

• Excel SUMPRODUCT function
The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
• Excel SUM function
The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…
• Excel MOD function
he Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….
• Excel COLUMN function
The Excel COLUMN function returns the first column number of the given cell reference.The syntax of the COLUMN function is as below:=COLUMN ([reference])….
• Excel ROW function
The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….

VLOOKUP Return Multiple Values Horizontally

This post will guide you how to vlookup a value and then return multiple corresponding values horizontally in Excel. How do I lookup a value and return multiple matched values in the same row in Excel. Or how to return multiple values with VLOOKUP function in Excel.

VLOOKUP Return Multiple Values Horizontally

Assuming that you have a list of data that contain product name and product sales in the range A1:B6, and you want to vlookup a product “excel”” and return all sales value of the “excel” horizontally.

We have talked that the VLOOKUP function can be used to return only a corresponding value of the first occurrence of a lookup value. And if you want to return multiple values horizontally, how to achieve it.

You can create a complex array formula based on the INDEX function, the SMALL function, the IF function, the ROW function and the COLUMN function.

`=INDEX(\$B\$2:\$B\$6, SMALL(IF(A\$2:\$A\$9="excel", ROW(\$A\$2:\$A\$6)-ROW(\$A\$2)+1), COLUMN(A1)))`

Type this formula into a blank cell and then press Ctrl+Alt+Enter shortcuts to change this formula as array formula. And then drag the AutoFill handle from Cell C1 to E1.

You will see that the multiple values have been extracted into the same row.

Related Functions

• Excel SMALL function
The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …
• Excel INDEX function
The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
• Excel ROW function
The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
• Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
• Excel COLUMN function
The Excel COLUMN function returns the first column number of the given cell reference.The syntax of the COLUMN function is as below:=COLUMN ([reference])….
• Excel VLOOKUP function
The Excel VLOOKUP function lookup a value in the first column of the table and return the value in the same row based on index_num position.The syntax of the VLOOKUP function is as below:= VLOOKUP (lookup_value, table_array, column_index_num,[range_lookup])….

How to convert column letter to number

This post will guide you how to convert a column letter to a column number using an excel formula. And how to write a user defined function to convert column letter to number in excel VBA.

Convert column letter to number using excel Formula

If you want to convert a column letter to number, you can use a combination of the COLUMN function and the INDIRECT function to create an excel formula.

Firstly, you need to construct a text string of cell reference, such as: B1, then you can use INDIRECT function to transform this text string to a normal cell reference, next, using the COLUMN function to get the column number. So you can write down the following formula:

=COLUMN(INDIRECT(B1 &”1″))

Let’s see how this formula works:

=B1&”1″

This formula will join a column number with “1” to construct as a new text string contains column letter. The returned value goes into the INDIRECT function as its argument.

=INDIRECT(B1 &”1″)

The INDIRECT function returns a valid cell reference from a text string returned by the above formula. Then the returned result is passed into the COLUMN function.

=COLUMN(INDIRECT(B1 &”1″))

This formula returns the column number for a cell reference that returned by the INDIRECT function. So it returns number 1.

Convert column number to letter with VBA user defined function

You can also create a new user defined function to convert column number to a column letter in Excel VBA:

1# click on “Visual Basic” command under DEVELOPER Tab.

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module named

4# paste the below VBA code into the code window. Then clicking “Save” button.

```Public Function ConLetterToNum(ColN)
ConLetterToNum = Range(ColN & 1).Column
End Function```

5# back to the current worksheet, then enter the below formula in Cell C1:

= ConLetterToNum (B1)

Related Formulas

• Extract text after first comma or space
If you want to get substring after the first comma character from a text string in Cell B1, then you can create a formula based on the MID function and FIND function or SEARCH function ….
• Convert column number to letter
If you want to convert column number to letter, you can use the ADDRESS function to get the absolute reference of one excel cell that contains that column number….

Related Functions

• Excel COLUMN function
The Excel COLUMN function returns the first column number of the given cell reference.The syntax of the COLUMN function is as below:=COLUMN ([reference])….
• Excel INDIRECT function
The Excel INDIRECT function returns the cell reference based on a text string, such as: type the text string “A2” in B1 cell, it just a text string, so you can use INDIRECT function to convert text string as cell reference….

Excel Column Function

This post will guide you how to use Excel COLUMN function with syntax and examples in Microsoft excel.

Description

The Excel COLUMN function returns the first column number of the given cell reference.

The COLUMN function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.

The COLUMN function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the COLUMN function is as below:

=COLUMN ([reference])

Where the COLUMN function arguments is:
Reference -This is an optional argument.  A reference to a cell or a range of cells for which you want to get the first column number.
Note: If the Array argument is omitted, the Excel COLUMN function will return the column number of the cell that the function is entered in.

Example

The below examples will show you how to use Excel COLUMN Lookup and Reference Function to return the column number of a cell reference.

#1 To get the number of column in B1 Cell, just using the following excel formula: =COLUMN ( )

#2 To get the number of column in the reference D1:F5, just using the following excel formula: =COLUMN(D1:F5)

More Excel Column Function Examples

• VLOOKUP Return Multiple Values Horizontally
You can create a complex array formula based on the INDEX function, the SMALL function, the IF function, the ROW function and the COLUMN function to vlookup a value and then return multiple corresponding values horizontally in Excel.…
• Sum Every Nth Row or Column
If you want to sum every nth rows in Excel, you can create an Excel Array formula based on the SUM function, the MOD function and the ROW function..….