vba Loop every row to extract out the last third columns

Question

Dear all,

I need help to extract from excel with huge number of row records (100K+) and columns from A to AP.

Every row last used cell appears in different columns

1. Extract 2nd last used columns such as higlighted: ACERecruit, FINANCE, IAP, SPORTS into 1 new column
2. Extract from 2nd column till last used 2nd column (concat join) such as:

• View or Edit will be the last column in every row.
• Expected 1 new column should return:

[For IG Support] Nominations for Overseas Developmental Programmes in 2022
2009S77 Claim ( Disbursement ) IAP
TDF01 / LEV3 / 221 - Gastro Beats 2000 - 2010

Many thanks ❤ struggling for awhile, well appreciated any help :))

Answer 1

look forward to helping you even a little.

Assuming that there are no spaces until the last column of each row,

Enter the following formula in the AQ2 cell.

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

The display should look like this:

[For IG Support] Nominations for Overseas Developmental Programs in 2022

Copy the AQ2 cell

Select the last row of AQ (same as the last row of column B) to AQ3 and press ent.

you can batch paste.

Copy the AQ column

Paste the value on another sheet.

Answer 2

hihi this solves the concatenation and is able to extract on an individual row basis.