Help with a formula

Beth 630

Hi there, I'm looking for a formula that would look at an Identifier Column and pull back the Amount Financed. But if the Identifier Column is repeated, would only pull back the first instance of the Amount Financed.

Here's a snippy of what I want that Quote Value column to look like. But I'd like to be able to use a column formula in the Quote Value column.

Can anyone assist? Thanks in advance :)

Paul Newcome

Happy to help. 👍️

Paul Newcome

You would insert an auto-number column with no special formatting (called "Auto" in this example) and then use this formula:

=IF(Auto@row = MIN(COLLECT(Auto:Auto, [Identifier Column]:[Identifier Column], @cell = [Identifier Column]@row)), [Amount Financed]@row)

Beth 630

@Paul Newcome Thank you so much for your response here but I can't quite get this to work. I'm adding the Auto column as you suggest and it's returning no value. Can I pick your brain some more?

Paul Newcome

You will need to save the sheet after adding the column before it will populate.

If you are saving the sheet and still facing challenges, some screenshots would be very helpful.

Beth 630

I did save the sheet - I can see the auto-number has populated. The formula is returning null - so it's not reading that there is a positive for the min/collect value

Paul Newcome

The Auto column shouldn't have any formatting applied to it. It should just be outputting numbers only.

If you are using the auto-number column elsewhere and need to keep the formatting, you will need to insert a new column called "Row", use this column formula:

=MATCH(Auto@row, Auto:Auto, 0)

Then in the IF/MIN/COLLECT formula replace all instances of Auto with Row.

Beth 630

@Paul Newcome That's it! I missed that you said "no special formatting" in your initial instructions. Thank you SO much for your quick assistance - you are a rock star!

Thank you!!!!

Paul Newcome

Happy to help. 👍️