Sean asks:

Sheet1

A 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 40

Oscar,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:

Debra Dalgleish has two interesting pages about organizing data in excel:

I created an user defined function that organizes non empty cells into rows, using a delimiting value. In the example below, "Country" is the delimiting value.


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.
  • Press Enter once.
  • Release all keys.

User defined function:

=OrganizeData(srch, rng)

srch - delimiting value

rng - Cell range

VBA code

Function OrganizeData(srch As String, rng As Variant)
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
ReDim temp(Range(Application.Caller.Address).Columns.Count - 1, 0)
chk = False
rng = rng.Value
For r = LBound(rng, 1) To UBound(rng, 1)
    For c = LBound(rng, 2) To UBound(rng, 2)
        If rng(r, c) = srch Then
            If chk <> False Then
                For ca = i To UBound(temp, 1)
                    temp(ca, UBound(temp, 2)) = ""
                Next ca
                i = 0
                ReDim Preserve temp(UBound(temp, 1), UBound(temp, 2) + 1)
            End If
            chk = True
        ElseIf rng(r, c) <> "" And rng(r, c) <> srch Then
            temp(i, UBound(temp, 2)) = rng(r, c)
            i = i + 1
        End If
    Next c
Next r
For ca = i To UBound(temp, 1)
    temp(ca, UBound(temp, 2)) = ""
Next ca
ReDim Preserve temp(UBound(temp, 1), UBound(temp, 2) + 1)
iRows = Range(Application.Caller.Address).Rows.Count
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
    OrganizeData = Application.Transpose(temp)
End Function

Download excel 2007 MacroEnabled workbook *.xlsm

organize-data.xlsm

Related posts:

Excel udf: Filter unique distinct values (case sensitive)

Excel recursive udf: List files in a folder and subfolders

Excel udf: Looking up data in multiple cross reference tables

Excel udf: Combine cell ranges into a single range while eliminating blanks

Adjust stock chart axis automatically