Forum Discussion

mickeymao's avatar
mickeymao
Copper Contributor
Feb 11, 2023

Find and concatenate random columns from second column to last used third column on every row

Hi all,

 

I have a file with 100K+ records where values are in multiple columns randomly from Column A to AG

Here is an example but not limited to the columns shown below.

 

Any help to:

1. Extract all second last column in every row and paste in another new column

e.g. 

102221 return "ACEWS"

102221245 return "HRRecruit"

 

2. Extract and concat values from columnB to last third column (need to find in every row e.g. ColumnG for RefID "102221245"). Copy and paste in another new column

102221245 return "Participant + Update Projects"

10222 return "Credits Frequently Ask Qns New Approach HSM"

 

Edit or View is always the last column in every row.

 

RefIDColumnBColumnCColumnDColumnEColumnFColumnGColumnHColumnI
102221text1ACEDelightACEWSEdit   
102221text1ACEDelightACEWSEdit   
102221245text2 Participant+UpdateProjectsHRRecruitEdit
102221245text3 Participant+UpdateProjectsHRRecruitEdit
102221241Budget&PlanningDevelopmentACPView  
102221241Budget&PlanningDevelopmentACPView  
10222CreditsFrequentlyAskQnsNewApproachHSMView
10222CreditsFrequentlyAskQnsNewApproachHSMView
         
         

 

Help is greatly appreciated. It's taking me many hours to figure out.

 

excel
Macros and VBA
  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Feb 13, 2023

    mickeymao 

    Hello, take a look at my example document. I have implemented your wishes with:

     

    =IFERROR(INDEX(A2:I2,LOOKUP(2,1/--(A2:I2<>""),COLUMN(A2:I2))-1),"")
=IFERROR(TEXTJOIN(" ",TRUE,INDEX(A2:I2,SEQUENCE(1,LOOKUP(2,1/--(A2:I2<>""),COLUMN(A2:I2))-3,2))),"")

     

    I hope it fits so far. It doesn't always come out what you said it would. There is still a mistake somewhere.

    A more modern version with XLOOKUP and XMATCH:

    =XLOOKUP("*",B2:I2,A2:H2,"",2,-1)
=IFERROR(TEXTJOIN(" ",TRUE,INDEX(A2:I2,SEQUENCE(1,XMATCH("*",A2:I2,2,-1)-3,2))),"")

     

     

    • mickeymao's avatar
      mickeymao
      Copper Contributor
      Feb 25, 2023

      dscheikeymany thanks for your help.

      I do see the formula having Name? issue after I tried applying the same.

      Since it contains unsupported like --, _xlfn, Sequence in your formula.

       

       

      However manage to get this working:

      =TEXTJOIN(" ",,B2:(INDIRECT(ADDRESS(ROW(B2),COUNTA(A2:P2)-2))))

       

Resources