Answer to Questions submitted to Ask Jason Khoo

Question:

I have a workbook that contains 2 sheets. I have a column of records that I need Excel to consolidate to only unique records to the 2nd sheets. Then I am using the sumif command to total those. for example:

The first sheet has records in column B and C:

On the second sheet I only want to see

 A              B

123 =sumif(sheet1'!A:A,a1,Sheet1'!B:B) 

145

165

137

178

I hope I made sense. I know about the Advanced filter for unique records, but I don't want to have to re-run the filter everytime I update Sheet1. I would like it to run just like anyother formula.

Answer:

First you have identify the first instance of the number. When it appears, it should be given a number 1 unit larger than the last. The trunc formula will make sure that the decimals are not included in addition. If the number is a repeat, then add only 0.01 to the number. To make sure that this is possible, you need to make sure that you start counting from the beginning all the time and increase the range as it goes down. See diagram below:

In the next sheet, start a series with an incremental of one. Then use vlookup to identify the value in the first sheet. Vlookup formula will return the value in the first sheet. Then do a sumif formula to find the total value related to the number found in the first sheet.

You can see the whole solution in this answer Hunter file