0

I have a CSV data export from an external system where a number of fields can hold multiple relevant values. In the UI the data for those fields appears comma delimited in a single column.

I'll periodically need to export this data to update the data in Excel 2016.

In the CSV export each additional value in those fields produces another column with the same field heading (e.g. Sprints | Sprints | Sprints) with each column having one of the values from the field (e.g. S1 | S2 | S3). The more values, the more repeated column headings.

Week 1 the data might look like this, and on the right the output I'd like: enter image description here

The next time I do an export the columns may change and the data may look like this. The imported data may even include more comp data columns. Again, by preferred output on the right. enter image description here

I've done some googling, but the concatenate solutions I see all relate to concatenating data based on the values in the columns, not the column heading.

MAX(B1:D1) and MAXA(B1:D1) ignored the text values and returned 0 for the range. It would also need the range set manually each time the number of columns in the CSV data changed

I need all the multiple values for each field with the same column heading/name in one Excel cell/column (e.g. S1 S2 S3) but have no control over the number of columns with the same column heading/name in any given export.

Alternatively I could possibly succeed with just the left most and right most column values (e.g. S1 and/or S3).

Ideally this would occur without any additional manual intervention to modify the range after each new import of the CSV data into Excel.

Dave F
  • 151
  • 14
  • Can you give some example of your input and expected output data? – Kamal Jul 10 '19 at 08:42
  • Sorry for the delay. Additional info added – Dave F Jul 16 '19 at 01:17
  • Can we assume that columns with same title/heading will be always together? – Kamal Jul 18 '19 at 10:07
  • I thought I'd replied. Sorry about that. hmmm... I wonder where that comment went. Anyway, The columns with the same heading will always be grouped together. However, they may have another column in between (e.g. A, A, A, B, C, D, D, D, E) – Dave F Jul 22 '19 at 22:51

1 Answers1

1

I don't have the best solution, but for now this should help.

Assuming your input data is in Sheet1 like this: enter image description here

Then in Sheet2,
1. In cell A1, manually add title same as A1 in Sheet1 or use formula =Sheet1!A1
2. For column headings use this formula in cell B1, extend it in same row until you get zeros:

=INDEX(Sheet1!1:1,1,LOOKUP(2,1/(Sheet1!$1:$1=A$1),COLUMN(Sheet1!$1:$1))+1)
  1. For row values, copy this formula in cell A2, extend it in both directions:
=TEXTJOIN(",",TRUE,INDIRECT(CONCATENATE("Sheet1!",ADDRESS(ROW(),MATCH(A$1, Sheet1!$1:$1,0)))):INDIRECT(CONCATENATE("Sheet1!",ADDRESS(ROW(),LOOKUP(2,1/(Sheet1!$1:$1=A$1),COLUMN(Sheet1!$1:$1))))))

This should get you output like this as expected:
enter image description here

Kamal
  • 2,384
  • 1
  • 13
  • 25
  • Thanks Kamal, The first formula (INDEX) works beautifully. Sheet2 shows each heading from Sheet1 only once. Nice work. The second formula (TEXTJOIN) returns #NAME? for every cell. – Dave F Jul 23 '19 at 02:20
  • On further investigation it looks like =TEXTJOIN is not a valid function on our SOE instance of Excel 2016 – Dave F Jul 23 '19 at 02:38
  • Sorry, I don't understand what is SOE instance of Excel 2016, I am using MS Office 365. – Kamal Jul 23 '19 at 02:50
  • SOE = Standard Operating Environment. Our OS and software are fairly tightly controlled. I have little flexibility to install applications on my workstation. – Dave F Jul 24 '19 at 05:53
  • Ok, see if you can use VBA like [here](https://stackoverflow.com/a/42165195/5319738) – Kamal Jul 24 '19 at 07:01
  • Thanks Kamal, that has brought me closer. I copied the linked formula into a new VB module. The concatenated columns now show data, but the all single columns now show #VALUE! error. ` key | summary | comp, t-shirt | sprint` ` #VALUE! | #VALUE! | car,bus | #VALUE! | S2` ` #VALUE! | #VALUE! | bus | VALUE! | S2,S3,S4` – Dave F Jul 25 '19 at 03:01