## Forum Discussion

Okay, in your source data such Id is transforming into number. When we wrap **group** by SUBSTITUTE it returns back as text. However, Id in filter is still the number. Since number <> text, formula finds nothing and returns an error.

The workaround could be to transform Id into the text the same way, i.e. use

`Id, SUBSTITUTE(CHOOSECOLS(monthRange, 1),"",""),`

When it shall return correct result. Entire formula will be

```
=LET(
date, CHOOSECOLS(range, 2),
rangeId, CHOOSECOLS(range, 1),
monthRange, FILTER(range,
(EOMONTH(date,0) = EOMONTH( DATE(year,monthNumber,1), 0 ))*
IF( monthID = "", 1, ( rangeId = monthID ) )
),
Id, SUBSTITUTE(CHOOSECOLS(monthRange, 1),"",""),
transaction, CHOOSECOLS(monthRange, 4),
type, CHOOSECOLS(monthRange, 5),
amount, CHOOSECOLS(monthRange, 6),
groupIndexes, {1,4,5},
group, SUBSTITUTE( UNIQUE( CHOOSECOLS(monthRange, groupIndexes ) ),"",""),
result,VSTACK({"Id","Transaction","Type","Amount"},
HSTACK(
group,
BYROW( group,
LAMBDA(v, SUM(
FILTER(amount,
(Id = INDEX(v,1,1) )*
(transaction = INDEX(v,1,2) )*
(type = INDEX(v,1,3) )
)
) )
)
) ), result
)
```

which is in With ID sheet attached.

Changed the source Id to text and full Id text. It all looks good now.

Thanks for the help

- Norman_McIlwaineOct 08, 2023Brass ContributorThank you for the quick and excellent response.
- Norman_McIlwaineSep 02, 2023Brass Contributor
Just noticed that when all id are selected (id field blank) the formula does not sum properly. i.e. the INTEREST rates in three id's are only summed within the id.

Source data

Output Data

Thanks for looking at this for me, if I can help please ask me.

- SergeiBaklanSep 02, 2023MVP
Sorry, I didn't catch. In your sample Source data for the ID="06962-5001375" and Transaction = "INTEREST" has only one record with amount -2.81. However in Output we have -20.59. Is that full data in your sample?

- Norman_McIlwaineSep 02, 2023Brass Contributor
Sorry I took so long to respond I had some business to attend to around the house.

I had forgotten that I was only looking at data for month 7 that was my mistake. So I selected only month 7 and interest from the full download (286 entries) and this is what I found with the id field empty.

- Norman_McIlwaineSep 02, 2023Brass ContributorBasically what I am looking at when the Id field is empty is the total amount for the Transaction and Type fields. I.e. Total Interest paid for the month and year requested, The id field in this case would be of no concern. However if I still wanted it to be of a concern is when I select an id with the month and year.
- SergeiBaklanSep 04, 2023MVP
I added "Any" in such case into first column

If you don't need it you may use

*IF(monthID="", DROP( result,,1), result )*instead of*result*at the end of the formula.First half of the formula is modified for such filtering`=LET( date, CHOOSECOLS(range, 2), rangeClean, HSTACK( SUBSTITUTE( DROP(range,,-1), "", "" ), TAKE(range,,-1) ), IdClean, SUBSTITUTE(monthID, "", ""), ifDates, (EOMONTH(date,0) = EOMONTH( DATE(year,monthNumber,1), 0 )), ifId, CHOOSECOLS(rangeClean, 1) = IdClean, monthRange, IF( IdClean = "", IFNA( HSTACK("Any", DROP( FILTER(rangeClean, ifDates ),,1) ), "Any"), FILTER(rangeClean, ifDates*ifId ) ), Id, CHOOSECOLS(monthRange, 1), transaction, CHOOSECOLS(monthRange, 4), type, CHOOSECOLS(monthRange, 5), amount, CHOOSECOLS(monthRange, 6), groupIndexes, {1,4,5}, group, UNIQUE( CHOOSECOLS(monthRange, groupIndexes ) ), result,VSTACK({"Id","Transaction","Type","Amount"}, HSTACK( group, BYROW( group, LAMBDA(v, SUM( FILTER(amount, (Id = INDEX(v,1,1) )* (transaction = INDEX(v,1,2) )* (type = INDEX(v,1,3) ) ) ) ) ) ) ), result )`

- Norman_McIlwaineSep 05, 2023Brass Contributor
You are defiantly the best in knowledge about Excel. The program works beautifully against the download. Now I am trying to massage the download and have completed several steps manually so far. However I have noticed that thee is an empty row in the column beside the one I want to move. I am trying to substitute what is in column H (which is empty) with the corresponding row value in G and I am running into a problem with the functions of SUBSTITUTE and IF working together. That is to say: SUBSTITUTE what is in Column H with the value in column G, IF the value in column H is "" .

Sample download massaged.

Formula

Thanks for all you help.

- SergeiBaklanSep 05, 2023MVP
Sorry, I missed a bit. Do you mean empty cell or empty row; columns E anf F or columns H and G?

Perhaps you may illustrate on small range what do you have initially and what would you like to have after some formula will be applied?

- Norman_McIlwaineSep 05, 2023Brass Contributor
Sorry, for the misunderstanding. It is the Cells in column F which are blank that I am trying to populate from the corresponding cell in column G.

Source data

Source Massaged Data

- SergeiBaklanSep 05, 2023MVP
I see, thank you. In general that's job for Power Query if you consider such option. It was created to transform data. If you initially get data from csv file or other source, you only need to say Power Query from which file/folder to take data, the rest could be done within query and loaded into the grid, includes result of the formula we discussed. Once done, the rest of the job to click Refresh All updating with new data.

If with formula, we may take entire data from one place, apply formulae and put result into another place. The question is to you need to edit something additionally within resulting range or not.

If massaged data is needed only to generate in bit another way result of the current formula, it's better to adjust that formula.

To replace data in place it could couple of options - OfficeScript if it is available in your subscription ( if so it shall be Automate tab on ribbon).

Or VBA programming, but that's not my territory.

So, all depends on goals and tools you have in hand. What will be desirable option?