johnmheinzel
New Member
I have data that was extracted, with a 'File' identifier in column A, and related 'Code' in column B. For some of the records the Code is on multiple rows, with only the first row showing the File. I'd like to have one row for each File with the code concatenated, if necessary.
For example, I'd like to turn this:
into this:
Given that I don't know how many rows of code need to be concatenated, but I do know I need to start with the row that has something in the File column, then concatenate the following rows if the File column is null, so it seems like a formula in a third column could do this, then I could convert the results to values and sort by the File column to get all my data together and delete the rest. A sample file is attached with the Raw Data column and what the cleaned data should look like.
My first attempt seems too messy. Any suggestion as to a formula that would work to concatenate correctly?
For example, I'd like to turn this:
File | Code |
PRODUCT | TRANS('PROD.BR',@ID,40,"X"); |
INDEX(@1,"ALL",1); | |
DCOUNT(@1[1,@2],@SVM); | |
TRANS('PROD.BR',@ID,25,"X")<1,1,@3> |
File | Code |
PRODUCT | TRANS('PROD.BR',@ID,40,"X");INDEX(@1,"ALL",1);DCOUNT(@1[1,@2],@SVM);TRANS('PROD.BR',@ID,25,"X")<1,1,@3> |
My first attempt seems too messy. Any suggestion as to a formula that would work to concatenate correctly?
Attachments
Last edited: