Ambitious formula-based column builder...

[Steve_K]

Hi all,

I've been working through some challenges and have a pretty ambitious goal here. Something tells me it may not be possible with formulas, but I remain hopeful.

I'd like to take the columns from one sheet and map them to other values in another sheet while also being able to re-order them into a different column order and to include "new" columns not in the original set as well. Here's an example of the goal.

On one sheet, I have the following range of source data:

Source Data Set

[TABLE="class: grid, width: 750"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Product Type[/TD]
[TD]Code[/TD]
[TD]Name[/TD]
[TD]Brand[/TD]
[TD]Price[/TD]
[TD]Cost[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]P[/TD]
[TD]XSJDHU-01[/TD]
[TD]Kukla Stylish Shades[/TD]
[TD]Kukla[/TD]
[TD]$3,000[/TD]
[TD]$750[/TD]
[TD]Great shades[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]P[/TD]
[TD]KDW-001s[/TD]
[TD]Kukla Deluxe Watch[/TD]
[TD]Kukla[/TD]
[TD]$500[/TD]
[TD]$100[/TD]
[TD]Awesome watch[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]P[/TD]
[TD]SKQOD-10[/TD]
[TD]Kukla Stylish Hoodie[/TD]
[TD]Kukla[/TD]
[TD]$320[/TD]
[TD][/TD]
[TD]Really comfortable[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]P[/TD]
[TD]SKDWJ-102[/TD]
[TD]Kukla Sports Watch[/TD]
[TD]Kukla[/TD]
[TD]$350[/TD]
[TD]$50[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

A couple points about this data:

1. Any field can contain any value; there are no connections between the data in these cells
2. This source data may or may not contain the same or a unique set of columns of any number
3. The column order may or may not be constant across source data sets
4. Some of the data may or may not have blanks (see F4, for example)

What I now need to do is to get the above data into a custom format in another sheet. More specifically, I need to:

1. Choose which columns I want to transfer
2. Map the column names in the above sheet to names of my choosing
3. Have the ability to leave out columns from the source data set so they're completely ignored in the result set
4. Have the ability to include a custom column header mapped from a source header without including source data beneath it
5. Have the ability to include new columns into the result set which I will fill in manually

As an example:

Result Data Set

[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]_type[/TD]
[TD]sku[/TD]
[TD]name[/TD]
[TD]brand[/TD]
[TD]price[/TD]
[TD]description[/TD]
[TD]custom_1[/TD]
[TD]another_custom[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]XSJDHU-01[/TD]
[TD]Kukla Stylish Shades[/TD]
[TD]Kukla[/TD]
[TD]$3,000[/TD]
[TD]Great shades[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]KDW-001s[/TD]
[TD]Kukla Deluxe Watch[/TD]
[TD]Kukla[/TD]
[TD]$500[/TD]
[TD]Awesome watch[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]SKQOD-10[/TD]
[TD]Kukla Stylish Hoodie[/TD]
[TD]Kukla[/TD]
[TD]$320[/TD]
[TD]Really comfortable[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]SKDWJ-102[/TD]
[TD]Kukla Sports Watch[/TD]
[TD]Kukla[/TD]
[TD]$350[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In this data transformation, the following has taken place:

1. The column headers have been mapped like so:
   1. Product Type --> _type
   2. Code --> sku
   3. Name --> name
   4. Brand --> brand
   5. Price --> price
   6. Description --> description
2. Although the "Product Type"'s column header has been renamed and that column has been transferred to the result data set, it's values from the source data set have not been transferred
3. The entire "Cost" column from the source data set (F) has been removed (e.g. not transferred) to the result set
4. The "Description" value in the source data set at G5 has been intentionally left blank in the source data set and kept blank in the result set
5. The fields "custom_1" and "another_custom" have been included in the result data set as column headers with no values

Regarding the last point above, I haven't even begun to figure out how to do the appending of the two custom fields with their values, so I can amend that requirement to say that I should have the ability to add custom headers with blank row values. (Exactly as what would be necessary for A:A in the result set above)

In thinking through the connections here, the only thing I can think of is some kind of "conversion" sheet for the source and result sheets. Something like this:

Conversion Table

[TABLE="class: grid, width: 350"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Source Header[/TD]
[TD]Result Header[/TD]
[TD]Blank[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Product Type[/TD]
[TD]_type[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Code[/TD]
[TD]sku[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Name[/TD]
[TD]name[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Brand[/TD]
[TD]brand[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Price[/TD]
[TD]price[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Cost[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Description[/TD]
[TD]description[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]custom_1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]another_custom[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In this "conversion table," we do the following:

1. Read in the columns from the source data set into A:A
2. User fills in value for "Result Header" which is used in the result table
3. If both the "Source Header" and "Result Header" fields are filled and the "Blank" column is marked, the Source Header value is converted into the Result Header value and included in the result data set; however, none of its values are transferred
4. If the "Source Header" field is filled in but the "Result Header" field is blank, that "Source Header" column should be excluded entirely from the result data set
5. If the "Source Header" field is blank but the "Result Header" field is filled, this indicates a manually-defined "custom field" column header which would not be present in the source data, but would be included in the result data set.

Whew - long one, but I hope I've explained the goal thoroughly and clearly. To this point, I've been experimenting with TRANSPOSE() and INDEX and MATCH combinations with only partial success. The main limitations of each have been:

1. TRANSPOSE is limited by its need for a fully-defined array. I need to be able to include a variably-sized source data set
2. INDEX and MATCH cannot seem to map the "horizontal-to-vertical-to-horizontal" approach in order to maintain the relationships between the source data and the converted data.

I am relatively convinced that this is possible and that I simply am not advanced enough to grasp it. I would appreciate any and all help, and I thank you in advance for taking the time to read this novella here.

 

[Peter_SSs]

It would seem to me that the results could be achieved BUT ..

1. Why would you be looking for a formula solution to this when a macro solution may be significantly simpler? .. or even manual: copy columns you want in the order you want, leaving spaces for any new columns you want, enter desired headings.

2. Approximately how many rows of data are there in the source sheet?

3. Is the source data really only 7 (or thereabouts) columns?

 

[Steve_K]

Hi Peter,

Fair questions, all!

1. I'd take a macro as well, but given that my needs will still require manual intervention, recording a macro cannot be done as the steps will not always be exact. If you mean writing VBA, I'd go for that, too; however, my VBA skills are lack-luster at best.

2. The most I've seen is about 1,000 (and I'd say that's a realistic bet), but there is no hard limit.

3. No, the column count can also be high (50-100+)

Thanks again for the reply and for helping to try and simplify here (as I assume that was the goal of your questions).

 

[Peter_SSs]

2. The most I've seen is about 1,000 (and I'd say that's a realistic bet), but there is no hard limit.

3. No, the column count can also be high (50-100+)

In that case I'd say a formula solution would be ..
a) Far too complex
b) Even if possible & implemented would impair the performance of the workbook so much it would be pretty much unusable.

1. I'd take a macro ...

The good news is that I think this can be done with a macro without too much trouble, provided you are happy with this implementation method.

1. Original data is on a sheet called "Data" (this & other sheet names can be easily edited in the code), exactly as shown in your original "Source Data Set".
2. Set up a "Conversion" sheet in your workbook. This is similar to your "Conversion Table" above, but not quite the same. The important points about this table are:
- The 'Result Header' column should hold the actual header values you want in the final results, in the correct order they are to appear, and with no blanks. That is, for your example where the original 'Cost' column is not required, don't include it at all in the conversion table.
- The Names in the 'Source Header' column must exactly match the names in row 1 of the "Data" sheet, so spelling, spaces etc are important.

Notice that I have mixed up the columns more in my example, and excluded data from one further column, to demonstrate (hopefully) the flexibility of the macro.

Excel Workbook
	A	B	C
1	Source Header	Result Header	Blank
2	Product Type	_type	X
3	Code	sku
4	Brand	brand	X
5	Name	name
6	Description	description
7		custom_1
8	Price	price
9		another_custom
10
Conversion

3. The code to perform the task is as below. It assumes sheet "Results" already exists but any data already on it can be removed.

Sub ExtractAndRearrange()
  Dim aResults, aConv, aCols, aRws
  Dim wsD As Worksheet, wsR As Worksheet, wsC As Worksheet
  Dim i As Long, BlnkCol As Long, LastRw As Long
  
  Set wsD = Sheets("Data"): Set wsR = Sheets("Results"): Set wsC = Sheets("Conversion") '<-Check names
  With wsC.Range("A1").CurrentRegion
    aConv = .Offset(1).Resize(.Rows.Count - 1).Value
  End With
  ReDim aCols(1 To UBound(aConv)) As Long
  With wsD
    LastRw = .Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, SearchFormat:=False).Row
    BlnkCol = .Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, SearchFormat:=False).Column + 1
    On Error Resume Next
    For i = 1 To UBound(aConv)
      aCols(i) = BlnkCol
      If Len(aConv(i, 1)) > 0 And aConv(i, 3) = vbNullString Then
        aCols(i) = .Rows(1).Find(What:=aConv(i, 1), LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False).Column
      End If
    Next i
    On Error GoTo 0
    aRws = Evaluate("row(1:" & LastRw & ")")
    aResults = Application.Index(.Cells, aRws, aCols)
  End With
  With wsR
    .UsedRange.ClearContents
    With .Range("A1").Resize(, UBound(aConv))
      .Resize(LastRw).Value = aResults
      .Value = Application.Transpose(Application.Index(aConv, 0, 2))
      .EntireColumn.AutoFit
    End With
  End With
End Sub

4. The result:

Excel Workbook
	A	B	C	D	E	F	G	H
1	_type	sku	brand	name	description	custom_1	price	another_custom
2		XSJDHU-01		Kukla Stylish Shades	Great shades		3000
3		KDW-001s		Kukla Deluxe Watch	Awesome watch		500
4		SKQOD-10		Kukla Stylish Hoodie	Really comfortable		320
5		SKDWJ-102		Kukla Sports Watch			350
Results

 

[Steve_K]

Hi Peter,

Thanks so much for this; it works very well, and I appreciate it very much. The one thing I thought might improve the approach offered is that, often times, the source data columns are both different and numerous. To that end, it would be tremendously helpful if the macro brought in the source data column headers into the Conversion sheet (as I'll be copying it in from the sheet it on which it comes), and then allowed me to edit them as you instruct. I used TRANSPOSE() to get the headers into the conversion sheet, then copied and pasted values only.

What do you think -- is this something we could add to the approach?

 

[Peter_SSs]

.. it would be tremendously helpful if the macro brought in the source data column headers into the Conversion sheet ..

That really needs to be a separate process as there is considerable manual input required after that and before the other code would be implemented.

Try this to clear the conversion table and enter the source data headers.
Check the sheet names in the code.

Sub ResetConversionTable()
  Sheets("Conversion").UsedRange.Offset(1).ClearContents
  Sheets("Data").UsedRange.Resize(1).Copy
  Sheets("Conversion").Range("A2").PasteSpecial Paste:=xlPasteValues, Transpose:=True
  Application.CutCopyMode = False
End Sub