I have a large spreadsheet with 11 columns of data. Column B is a reference
number, column F is an amount attached to the reference number. There may be
multiple amount entries attached to a reference number but they are all
individual data lines.

What I would like to do is a formula or macro that checks column B and if
there are multiple entries for the same reference number, adds up the amounts
and if the 2 or more of the entries equals 0 to put an indicator in the next
empty column L.

Has amyone any idea how to do this as I am currently doing this manually and
with a 50,000 odd lines of data, it takes forever.

Any assistance gratefully accepted.

Thanks

Shazzmo

That would be a SUMIF.

=IF(sumif(B:B,B2,F:F)=0,"Zero",sumif(B:B,B2,F:F))

would return "Zero" if the sum is 0. Otherwise it would return the sum of
the values.

"Shazaxacpcc" wrote:

I have a large spreadsheet with 11 columns of data. Column B is a reference
number, column F is an amount attached to the reference number. There may be
multiple amount entries attached to a reference number but they are all
individual data lines.

What I would like to do is a formula or macro that checks column B and if
there are multiple entries for the same reference number, adds up the amounts
and if the 2 or more of the entries equals 0 to put an indicator in the next
empty column L.

Has amyone any idea how to do this as I am currently doing this manually and
with a 50,000 odd lines of data, it takes forever.

Any assistance gratefully accepted.

Thanks

Shazzmo

Sean,

I can see where you are coming from with this but I don't think I explained
my problem clearly.

The spreadsheet looks like this:



"Sean Timmons" wrote:

That would be a SUMIF.

=IF(sumif(B:B,B2,F:F)=0,"Zero",sumif(B:B,B2,F:F))

would return "Zero" if the sum is 0. Otherwise it would return the sum of
the values.

"Shazaxacpcc" wrote:

I have a large spreadsheet with 11 columns of data. Column B is a reference
number, column F is an amount attached to the reference number. There may be
multiple amount entries attached to a reference number but they are all
individual data lines.

What I would like to do is a formula or macro that checks column B and if
there are multiple entries for the same reference number, adds up the amounts
and if the 2 or more of the entries equals 0 to put an indicator in the next
empty column L.

Has amyone any idea how to do this as I am currently doing this manually and
with a 50,000 odd lines of data, it takes forever.

Any assistance gratefully accepted.

Thanks

Shazzmo

Sean,

Sorry didn't finish last entry.

The reference numbers are numerous (over 50,000) and they are not all the
same. Only some are multiple entries and the amounts do not always equal 0.
E.g.

Refernce Number Amount

12345 £250.00
12345 £235.00
12345 -£235.00
34567 £45.00
76543 £46.00
76543 -£46.00

I am trying to identify only those entries where the reference number is the
same and the amounts, whether 2 or more, equal 0.

Would a change to the formula do this?

Thanks a ton

Shazzmo

"Sean Timmons" wrote:

That would be a SUMIF.

=IF(sumif(B:B,B2,F:F)=0,"Zero",sumif(B:B,B2,F:F))

would return "Zero" if the sum is 0. Otherwise it would return the sum of
the values.

"Shazaxacpcc" wrote:

I have a large spreadsheet with 11 columns of data. Column B is a reference
number, column F is an amount attached to the reference number. There may be
multiple amount entries attached to a reference number but they are all
individual data lines.

What I would like to do is a formula or macro that checks column B and if
there are multiple entries for the same reference number, adds up the amounts
and if the 2 or more of the entries equals 0 to put an indicator in the next
empty column L.

Has amyone any idea how to do this as I am currently doing this manually and
with a 50,000 odd lines of data, it takes forever.

Any assistance gratefully accepted.

Thanks

Shazzmo

=IF(countif(B:B,B2)=1,"one
entry",if(sumif(B:B,B2,F:F)=0,"Zero",sumif(B:B,B2, F:F)))

Would account for single entries.

If you want to have a separate list to show one row per reference number,
highlight your reference column, go to Data - Filter - Advanced filter, click
unique records only, click the cop to another place and have it paste to a
blank spot of your sheet.

best to

"Shazaxacpcc" wrote:

Sean,

Sorry didn't finish last entry.

The reference numbers are numerous (over 50,000) and they are not all the
same. Only some are multiple entries and the amounts do not always equal 0.
E.g.

Refernce Number Amount

12345 £250.00
12345 £235.00
12345 -£235.00
34567 £45.00
76543 £46.00
76543 -£46.00

I am trying to identify only those entries where the reference number is the
same and the amounts, whether 2 or more, equal 0.

Would a change to the formula do this?

Thanks a ton

Shazzmo

"Sean Timmons" wrote:

That would be a SUMIF.

=IF(sumif(B:B,B2,F:F)=0,"Zero",sumif(B:B,B2,F:F))

would return "Zero" if the sum is 0. Otherwise it would return the sum of
the values.

"Shazaxacpcc" wrote:

I have a large spreadsheet with 11 columns of data. Column B is a reference
number, column F is an amount attached to the reference number. There may be
multiple amount entries attached to a reference number but they are all
individual data lines.

What I would like to do is a formula or macro that checks column B and if
there are multiple entries for the same reference number, adds up the amounts
and if the 2 or more of the entries equals 0 to put an indicator in the next
empty column L.

Has amyone any idea how to do this as I am currently doing this manually and
with a 50,000 odd lines of data, it takes forever.

Any assistance gratefully accepted.

Thanks

Shazzmo