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.

How to Transpose every N rows from one column to multiple1

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

How to Transpose every N rows from one column to multiple2

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.

How To Transpose Every N Rows of Data into Muliptle Columns vba 1.png

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

How To Transpose Every N Rows of Data into Muliptle Columns vba2.png

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

How To Transpose Every N Rows of Data into Muliptle Columns vba3.png
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.

How To Transpose Every N Rows of Data into Muliptle Columns vba5.png

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

How To Transpose Every N Rows of Data into Muliptle Columns vba6.png

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.

How To Transpose Every N Rows of Data into Muliptle Columns vba7.png

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.

How To Transpose Every N Rows of Data into Muliptle Columns vba8.png

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.

Address of first cell in range1

Use the following Formula:

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

Address of first cell in range1

Note: A1:E9 is array of tables

Let’s See How This Formula Works:

= COLUMN(A1:E9)

Address of first cell in range1

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

= ROW(A1:E9)

Address of first cell in range1

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))

Address of first cell in range1

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.

Address of first cell in range1

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))),"")

excel multiple matches into separate column1

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}

excel multiple matches into separate column1

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}

excel multiple matches into separate column1

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.”

excel multiple matches into separate column1

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))

count row that contain specific value1

Let’s See That How This Formula Works:

=--(B2:C6=60)

count row that contain specific value2

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}

count row that contain specific value3

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:

Sum Every Nth Column 1

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).

Sum Every Nth Column 2

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

Sum Every Nth Column 3

(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.

Sum Every Nth Column 4

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).

Sum Every Nth Column 5

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.

Sum Every Nth Column 6

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.

Sum Every Nth Column 7

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

Sum Every Nth Column 8

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.

Sum Every Nth Column 9

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

Sum Every Nth Column 10

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.

Sum Every Nth Column 11

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.)

Sum Every Nth Column 12

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.

Sum Every Nth Column 13

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

Sum Every Nth Column 14

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.

Sum Every Nth Column 15

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:

Sum Every Nth Column 16

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.

Sum Every Nth Column 17

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

Sum Every Nth Column 18

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}.

Sum Every Nth Column 19

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

Sum Every Nth Column 20

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

Sum Every Nth Column 21

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}.

Sum Every Nth Column 22

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}.

Sum Every Nth Column 23

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.

Sum Every Nth Column 24

Step 2: Execute MOD function.

Sum Every Nth Column 25

Step 3: Compare values with 0 and convert TRUE/FALSE to 1/0.
Sum Every Nth Column 26

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.

Sum Every Nth Column 27

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.
Get the position of the nth using excel vba1

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

Step3: click “Insert” ->”Module” to create a new module.
export each sheet to csv2

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

Select and Copy Cell Values from Every Nth Column in Excel1

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.

Select and Copy Cell Values from Every Nth Column in Excel2

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

Select and Copy Cell Values from Every Nth Column in Excel3

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

Select and Copy Cell Values from Every Nth Column in Excel4

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

Select and Copy Cell Values from Every Nth Column in Excel5

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.

Select and Copy Cell Values from Every Nth Column in Excel6

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

Select and Copy Cell Values from Every Nth Column in Excel7

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.

increase cell reference by x rows1

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.

increase cell reference by x rows2

 

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 active cell address1

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.

get active cell address2

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

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range("B1").Value = ActiveCell.Address
End Sub

get active cell address3

#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.

get active cell address4

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.

convert multiple rows into a single row1

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.

Get the position of the nth using excel vba1

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

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

convert column number to letter3

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

convert multiple rows into a single row2

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.

convert multiple rows into a single row3

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

convert multiple rows into a single row4

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

convert multiple rows into a single row5

#8 Let’s see the result:

convert multiple rows into a single row6

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.

convert column letter to number1

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.

Get the position of the nth using excel vba1

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

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

convert column number to letter3

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

convert column letter to number2

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.

convert column letter to number3

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

convert column letter to number4

#7 let’s see the result:

convert column letter to number5

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.

convert column letter to number6

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 column letter to number7

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:

convert column letter to number8

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.

sum every nth row or column1
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)

sum every nth row or column2

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.

vlookup return multple values horizontally1

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″

convert column letter to column1

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″))

convert column letter to column2

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.

Get the position of the nth using excel vba1

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

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

convert column number to letter3

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

convert column letter to column3

 

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

= ConLetterToNum (B1)

convert column letter to column4


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 ( )

excel columns function example1

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

excel columns function example2

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..….