Hello. I am unsure the best way to ask this question, but I have a data set or table that I manually fill in with specific quantities. In order to use this data, I need the data to be reformatted/reorganized into a different format to be used in a different application. I would like this to be automated, but I have been unable to find a lookup or filter formula that can automate the generation of this table.
I have attached an example spreadsheet. The information in Columns A-G is manually entered, but I would like the information in Columns J-M to auto populate.
Re: Autogenerating a table from manually entered data
VBA solution.
this code is put in sheet event, to trigger any change in range format 1, to update in range format 2
(Right click on tab's name, view code, then paste below code into
PHP Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr&, i&, j&, k&, rng, res(1 To 100000, 1 To 4)
lr = Cells(Rows.Count, "A").End(xlUp).Row
If Intersect(Target, Range("A2:G" & lr)) Is Nothing Then Exit Sub
rng = Range("A2:G" & lr)
For j = 3 To UBound(rng, 2)
For i = 2 To UBound(rng)
If rng(i, j) <> "" Then
k = k + 1
res(k, 1) = rng(1, j): res(k, 2) = rng(i, 1)
res(k, 3) = rng(i, 2): res(k, 4) = rng(i, j)
End If
Next
Next
Range("J3:M100000").ClearContents
Range("J3").Resize(k, 4).Value = res
End Sub
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks