0

I would like to do something like this (Formula to find the header index of the first non blank cell of a range in Excel?) except that I want to capture all the nonblank cells.

An application of what I am expecting would produce column "prod"

    2   3   5   7   11  13      | prod |                    
2   1                            2^1
3       1                        3^1
4   2                            2^2
5           1                    5^1
6   1   1                        2^1 3^1
7               1                7^1
8   3                            2^3
9       2                        3^2
10  1       1                    2^1 5^1
11                  1            11^1
12  2   1                        2^2 3^1
13                      1        13^1
14  1           1                2^1 7^1
15      1   1                    3^1 5^1
16  4                            2^4

I wouldn't mind a result with multiple separators ie. 6= 2^1*3^1**** , as they could be removed.

cxs
  • 28
  • 7

1 Answers1

0

This user defined function will stitch together the header value with the range value.

In a standard public module code sheet:

Option Explicit

Function prodJoin(rng As Range, hdr As Range, _
                  Optional op As String = "^", _
                  Optional delim As String = " ")
    Dim tmp As String, i As Long

    For i = 1 To rng.Count
        If Not IsEmpty(rng.Cells(i)) Then
            tmp = tmp & delim & hdr.Cells(i).Text & op & rng.Cells(i).Text
        End If
    Next i

    prodJoin = Mid(tmp, Len(delim) + 1)
End Function

On the worksheet as,

enter image description here

If you absolutely must use worksheet functions then stitch 6 conditional concatenations together.

=TRIM(IF(B2, B$1&"^"&B2&" ", TEXT(,))&
      IF(C2, C$1&"^"&C2&" ", TEXT(,))&
      IF(D2, D$1&"^"&D2&" ", TEXT(,))&
      IF(E2, E$1&"^"&E2&" ", TEXT(,))&
      IF(F2, F$1&"^"&F2&" ", TEXT(,))&
      IF(G2, G$1&"^"&G2&" ", TEXT(,)))
  • thank you (very clear and everything parametrized). is it impossible to do it in a formula? (why) – cxs Apr 02 '18 at 19:48
  • You could do it in 6 formulas stitched together into one long formula but a udf is cleaner. –  Apr 02 '18 at 22:11
  • well yes, 1 formula per cell, which wouldn't be scalable, as this example would require. thanks – cxs Apr 03 '18 at 00:59