Author: Oscar Cronquist Article last updated on February 01, 2019

Sean asks:
Sheet1A B C D
8 Country Europe
9 Lights 100
10 Type A 200
11
12 Country USA
13 Fuel 40
14 Diesel 200
15
16 Europe Lights Type A 100
17 USA Fuel Diesel 40Oscar,is there a way to organize this the information into a database format like row 16 onwards,
It picks up all non blanks between the countries putting each line into a separate column.

Answer:

I created a User Defined Function that rearranges non empty cells into rows, using a delimiting value. In the example below, "Country" is the delimiting value. The desired output is displayed in row 11 and 12 and the UDF is shown in row 15 and 16.

A User Defined Function is a custom function that anyone can use, simply copy the VBA code and paste to a code module in your workbook.

Array formula in cell A15:F17

=OrganizeData("Country", A2:C8)

How to enter array formula in cell range A15:F17

  • Select cell range A15:F17.
  • Type =OrganizeData("Country", A2:C8)
  • Press and hold CTRL + SHIFT simultaneously.
  • Press Enter once.
  • Release all keys.

User defined Function Syntax

OrganizeData(srch, rng)

Arguments

srch Required. A delimiting value.
rng Required. The range containing values you want to rearrange.

VBA code

'Name User Defined Function
Function OrganizeData(srch As String, rng As Variant)

'Declare variables and data types
Dim cell As Range, temp() As Variant, ca As Single
Dim iRows As Integer, i As Integer, c As Single, r As Single
Dim chk As Boolean

'Make array temp as large as the cell range you entered the UDF in
ReDim temp(Range(Application.Caller.Address).Columns.Count - 1, 0)

'Save False to variable chk
chk = False

'Save values in cell range rng to array variable rng
rng = rng.Value

'Iterate through rows in rng variable
For r = LBound(rng, 1) To UBound(rng, 1)
    
    'Iterate through columns in array variable
    For c = LBound(rng, 2) To UBound(rng, 2)

        'If rng value is equal to delimiting value
        If rng(r, c) = srch Then

            'If Chk variable is not equal to False
            If chk <> False Then

                'Save blanks to temp variable based on value i
                For ca = i To UBound(temp, 1)
                    temp(ca, UBound(temp, 2)) = ""
                Next ca

                'Reset i to 0 (zero)
                i = 0

                'Increase array variable temp by 1 
                ReDim Preserve temp(UBound(temp, 1), UBound(temp, 2) + 1)
            End If

            'Save True to variable chk
            chk = True

        'If rng variable is not equal to nothing and rng variable is not equal to delimiting value then
        ElseIf rng(r, c) <> "" And rng(r, c) <> srch Then

            'Save value to array variable temp
            temp(i, UBound(temp, 2)) = rng(r, c)

            'Increment i with 1
            i = i + 1
        End If
    Next c
Next r
'Save blanks to remaining values in array variable temp
For ca = i To UBound(temp, 1)
    temp(ca, UBound(temp, 2)) = ""
Next ca

'Increase containers in arrat variable temp with 1
ReDim Preserve temp(UBound(temp, 1), UBound(temp, 2) + 1)

'Count the number of rows you have entered the UDF in
iRows = Range(Application.Caller.Address).Rows.Count

'Save blanks to remaining cells
For r = UBound(temp, 2) To iRows
    For c = LBound(temp, 1) To UBound(temp, 1)
        temp(c, r) = ""
    Next c
    ReDim Preserve temp(UBound(temp, 1), UBound(temp, 2) + 1)
Next r

'Return values in temp to worksheet rearranged vertically
OrganizeData = Application.Transpose(temp)

End Function

Where to copy the code?

  1. Copy VBA code above.
  2. Press Alt+ F11 to open the Visual Basic Editor.
  3. Press with left mouse button on "Insert" on the top menu.
  4. Press with left mouse button on "Module" to create a module.
  5. Paste code to module
  6. Exit VBE and return to Excel

Get the Excel file


organize-data.xlsm

Weekly Blog EMAIL

[newsletter_signup_form id=1]
Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

Filter unique distinct values (case sensitive) [UDF]

The User Defined Function demonstrated in the above picture extracts unique distinct values also considering lower and upper case letters. […]

Create a dynamic named range

A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves […]

Count unique distinct values in an Excel Pivot Table

ExcelBeginner asks: I have a small problem that I am not sure on how to solve. I now have a […]

Dynamic Gantt charts

Today I am going to show you how to create a dynamic Gantt chart in excel 2007. A Gantt chart helps […]

Prepare data for Pivot Table – How to split concatenated values?

This article demonstrates a macro that allows you to rearrange and distribute concatenated values across multiple rows in order to […]

How to use the NOMINAL function

The NOMINAL function calculates the nominal annual interest rate based on the effective rate and the number of compounding periods […]