Reorganize data [UDF]
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
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?
How to count word frequency in a cell range [UDF]
This user defined function creates a unique distinct list of words and how many times they occur in the selected […]
Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]
Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]
List files in a folder and subfolders [UDF]
This article demonstrates a user defined function that lists files in a ggiven folder and subfolders. A user defined function is […]
Search for a file in folder and subfolders [UDF]
The image above demonstrates a user-defined function in cell range B6:D7 that allows you to search a folder and subfolders […]
Split words in a cell range into a cell each [UDF]
This post describes how to split words in a cell range into a cell each using a custom function. I […]
Split values equally into groups
Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]
Filter unique distinct words from a cell range [UDF]
This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]
Count unique distinct values by cell color
This article demonstrates a User Defined Function (UDF) that counts unique distinct cell values based on a given cell color. […]
Substitute multiple text strings [UDF]
The SUBSTITUTE and REPLACE functions can only handle one string, the following User-Defined Function (UDF) allows you to substitute multiple […]
List permutations without repetition [UDF]
This blog post describes how to create permutations, repetition is NOT allowed. Permutations are items arranged in a given order meaning […]
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.