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.

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