Replace and update columns in excel-CodePudding

Table

    A     B     C     D     E
1  Jan   Feb  Mar    Apr   Jun
2   24   32    64    54     45
3   66   76    76    76     87
4   45   65    84    67     23

the last 3 columns should automatically update in another column as

Last 3 moths data

    AN        AO       AP
1   Month1  Month2   Month3
2     64     54        45
3     76     76        87
4     84     67        23

If I add another column in the first table as july in columns F. Then it should automatically replace the data in Month3 in second table and month3 data sholud move to month2 and month2 to month1.


=IF(AN2="",AL2,IF(AO2="",AM2,IF(AP2="",AN2,IF(AQ2="",AO2,IF(AQ2<>"",AP2,IF(AP2<>"",AO2,IF(AO2<>"",AN2,IF(AN2<>"",AM2,""))))))))

The formula is simlar to the another columns.

CodePudding user response：

With any version of Excel:

You can detect where the columns end using many methods - the simplest is using Count.

Enter the following into the top left of your result table:

=INDEX(2:2,,COUNT(2:2)-2)

This counts the number of cells with data in, and returns the data from that numbered column (offset to the left by 2). In the next cell to its right, change the -2 to -1. In the last cell, change the -1 to -0 or remove that part entirely.

Then copy the cells down to match your source table.

CodePudding user response：

If you have Excel-365 then use below formula.

=INDEX(A1:Z4,SEQUENCE(ROWS(A1:A4)),SEQUENCE(1,3,MAX(IF(A1:Z1<>"",COLUMN(A1:Z1),""))-2))

If you don't have Excel-365 then use below formula then drag down and across as required.

=INDEX($A$1:$Z$4,ROW($A1),MAX(IF($A$1:$Z$1<>"",COLUMN($A$1:$Z$1),""))-3 COLUMN(A$1))

CodePudding user response：

If you have ms365, try to use FILTER(), for example:

Formula in G1:

=FILTER(Table1[#All],COLUMN(Table1[#Headers])>COLUMNS(Table1[#Headers])-3)

If you add columns:

If you remove columns: