Forum Discussion
Antonino2023
May 23, 2023Brass Contributor
How to convert csv list to column WITHIN an excel Table (no VBA)
Hello,
I am looking for an equation that does the following:
Change Group | Change Group Conversion |
5,6,7,8,9 | 5 |
5,6,7,8,9 | 6 |
5,6,7,8,9 | 7 |
5,6,7,8,9 | 8 |
5,6,7,8,9 | 9 |
Essentially, the table from above is an excel Table (lets call it "Analysis_T"). I am looking for a single formula that can pull out the i-th value in the csv list where i is the data row # of Analysis_T. For example, row 1 should pull out "5", row 2 should pull out "6", and so on. The cells in the "Change Group" column will always be the same list as shown. What should be different is the adjacent value in the "Change Group Conversion" column as specified (both columns should remain as text).
Does anyone have suggestions for this task?
Assign the following macro to the Add shape:
Sub RoundedRectangle2_Click() Dim wk As String Dim rg As Range Dim tm As Date If Intersect(Range("B4:B10000"), ActiveCell) Is Nothing Then MsgBox "Please select a cell with working hours in column B!", vbInformation Exit Sub End If If ActiveCell.Offset(0, -1).Value = "" Then MsgBox "Please select a cell with a week number to the left!", vbExclamation Exit Sub End If If Val(ActiveCell.Value) <= 0 Then MsgBox "Please enter a valid working time!", vbInformation Exit Sub End If wk = ActiveCell.Offset(0, -1).Value Set rg = Range("E2:BE2").Find(What:=wk, LookAt:=xlWhole) If rg Is Nothing Then MsgBox "Week number not found!", vbInformation Exit Sub End If tm = Val(ActiveCell.Value) With Cells(ActiveCell.Row, rg.Column) .Value = .Value + tm End With End Sub
1 Reply
Sort By
Perhaps
=IFERROR(INDEX(TEXTSPLIT([@[Change Group]], ","), ROW([@[Change Group]])-ROW(Analysis_T[[#Headers],[Change Group]])), "")