I am looking to combine automatically all values from ranges of cells into A new format range of cells using Excel formulas, and I wonder if it's possible. Thanks

I have uploaded the file in google sheet for convenience

CodePudding user response：

Here a solution. Your input data and rules seem to have some inconsistences. I check my result for formula 1 is the same as @P.b, so it seem we have the same understanding, but your question and data need to be reviewed.

Here is the formula 1 in O4:

=LET(setY, FILTER(A4:C36, (B4:B36<>"") * (C4:C36<>"")), 
  amountY, FILTER(D4:D36, D4:D36<>""), 
  setX, FILTER(HSTACK(A4:A36, E4:F36), (E4:E36<>"") * (F4:F36<>"")), 
  amountX, FILTER(G4:G36, G4:G36<>""), 
  lkupY, BYROW(setY, LAMBDA(rowY, CONCAT(rowY))), 
  lkupX, BYROW(setX, LAMBDA(rowX, CONCAT(rowX))), 
  notMatchXInY, ISNA(XMATCH(lkupX, lkupY)), 
  SORT(IFERROR(VSTACK(HSTACK(setY, XLOOKUP(lkupY,lkupX, amountX), amountY), 
  FILTER(HSTACK(setX, amountX, XLOOKUP(lkupX,lkupY, amountY)), notMatchXInY)),""),1))

and here is the output:

Note: In light yellow (columns E:G) data that I think were miss placed in the input data from the question.

Formula 2 is just a partial result from the data in formula 1:

=LET(setY, FILTER(A4:C36, (B4:B36<>"") * (C4:C36<>"")),  
  setX, FILTER(HSTACK(A4:A36, E4:F36), (E4:E36<>"") * (F4:F36<>"")),
  lkupY, BYROW(setY, LAMBDA(rowY, CONCAT(rowY))), 
  lkupX, BYROW(setX, LAMBDA(rowX, CONCAT(rowX))), 
  notMatchXInY, ISNA(XMATCH(lkupX, lkupY)), 
  VSTACK(setY, FILTER(setX, notMatchXInY))
)

or filtering by the three first columns from formula 1 result, i.e.:

LET(formula2, FILTER(formula1, {1,1,1,0,0}), formula2)

where formula1 represents the output of formula 1.

The output will just the first three columns from the result of formula 1 (but without sorting, but it can be added, if needed)

Explanation

I use the following suffixes to identify each set or related calculations:

• X for Old Data
• Y for New Data

We first filter by non empty rows of both sets: SetY, SetX because we are going to work with them:

setY, FILTER(A4:C36, (B4:B36<>"") * (C4:C36<>""))
setX, FILTER(HSTACK(A4:A36, E4:F36), (E4:E36<>"") * (F4:F36<>""))

next we define the lookup variables, via concatenation of the search criteria:

lkupY, BYROW(setY, LAMBDA(rowY, CONCAT(rowY)))
lkupX, BYROW(setX, LAMBDA(rowX, CONCAT(rowX)))

The corresponding amount for each set:

amountY, FILTER(D4:D36, D4:D36<>"")
amountX, FILTER(G4:G36, G4:G36<>"") 

Because the resulting output will be the unique values (Year, Class and Type) from both sets, we need to find the elements in X not in Y and for that we use this variable:

notMatchXInY, ISNA(XMATCH(lkupX, lkupY))

The rest is just to build the final result using VSTACK, HSTACK to build the result in the form we want.

We use XLOOP function to apply the following business rules: For elements in setY found in setX, we use amountX and amountY respectively. All the #N/A values (not found case) are replaced at the end with an empty string (""). So this rule can be implemented as follow:

HSTACK(setY, XLOOKUP(lkupY,lkupX, amountX), amountY)

For the second set (Old Set). Elements from SetX not in SetY we obtain the amount as follow:

FILTER(HSTACK(setX, amountX, XLOOKUP(lkupX,lkupY, amountY)), notMatchXInY)

We use XLOOKUP in a similar way for previous setY and combine it with HSTACK. The only difference is to exclude setX elements already present in setY and for that we use a FILTER.