I have a spreadsheet that I update monthly to track sales by company. I
originally got the format from a former co-worker and couldn't figure out if
he maintained it manually or if there was a more automated way of updating it
each month. It's basically set up like this:

Column A = Company Name
Column B = September Revenue
Column C = October Revenue
Column D = November Revenue
etc.

There are hundreds of rows of individual companies and each month I have to
add a new column for the month's revenues. What I've been doing is pasting
in the newest month's list of revenue by company (for instance, November) to
the end of the data already in the spreadsheet, moving the list of new
revenue over to the November column, and then sorting everything by company
name (col. A). Many of the companies are existing clients that buy each
month so I end up with:
Company Sep Oct Nov
Row 1 = Jones Harbor LLC $200 $200
Row 2 = Jones Harbor LLC $200
Row 3 = English Brakes Inc. $175 $900
Row 4 = English Brakes Inc. $350
Row 5 = Greene & Sons $550
etc.

I have to manually move the revenue in the Nov. column up one row to the row
containing the continuous data. (i.e., I'd move the $200 in cell D2 up to D1
and D4 up to D3.) I'd prefer not doing it manually as it takes forever and
leaves room for error. Is this even possible?

Oh wow yes..

Keep your november revenue in a separate table.

In your new November column, type:

=IF(iserror(VLOOKUP(A2,G:H,2,0),"",VLOOKUP(A2,G:H, 2,0))

This says you are looking for the value in cell A2 (Jones Harbor LLC) in
your new table. I'm assuming here that the new table is in columns G and H.
Then, assuming the November value is in the 2nd column, I ask for the 2ngd
column to be returned. I only want the value returned if the names match
exactly, thus the ,0. If the company doesn't have a Novemebr value, return
"", a null cell.

Then, a 2nd step would be to do a countif(G2,,A:A). For any cell with a 0,
add this to the bottom of your master list as this is a new company in
November...

"blucajun" wrote:

I have a spreadsheet that I update monthly to track sales by company. I
originally got the format from a former co-worker and couldn't figure out if
he maintained it manually or if there was a more automated way of updating it
each month. It's basically set up like this:

Column A = Company Name
Column B = September Revenue
Column C = October Revenue
Column D = November Revenue
etc.

There are hundreds of rows of individual companies and each month I have to
add a new column for the month's revenues. What I've been doing is pasting
in the newest month's list of revenue by company (for instance, November) to
the end of the data already in the spreadsheet, moving the list of new
revenue over to the November column, and then sorting everything by company
name (col. A). Many of the companies are existing clients that buy each
month so I end up with:
Company Sep Oct Nov
Row 1 = Jones Harbor LLC $200 $200
Row 2 = Jones Harbor LLC $200
Row 3 = English Brakes Inc. $175 $900
Row 4 = English Brakes Inc. $350
Row 5 = Greene & Sons $550
etc.

I have to manually move the revenue in the Nov. column up one row to the row
containing the continuous data. (i.e., I'd move the $200 in cell D2 up to D1
and D4 up to D3.) I'd prefer not doing it manually as it takes forever and
leaves room for error. Is this even possible?

Ok, I understand what the formula is doing and that will possibly work.
However, I left out some information that will definitely affect your formula
(though I didn't realize it would be that significant at the time). How
would the formula be modified if there were other columns in play? For
instance, what if I have one company with three locations (each company on a
separate row, columns B, C, & D would be the corresponding City, State and
Requesting State (where the work was done)

Company City State Req. St. Sep Oct
Nov
Jones Harbor LLC Jackson MS MS 100 100
Jones Harbor LLC Tulsa OK TX 200
Jones Harbor LLC Tulsa OK TX
100
English Brakes Inc. Atlanta GA KY 100
English Brakes Inc. Atlanta GA WY
175

This is actually how my data is set up. I would need to have the Nov. total
for the third entry for Jones Harbor in Tulsa, OK brought up to the same line
as the second one. But for English Brakes, I would not be moving the Nov.
total up with the other line because they have different Requesting states.
Is this still impossible?

Thanks so much for your help on this. It will be a true timesaver if I can
get this to work properly.

Debbi


"Sean Timmons" wrote:

Oh wow yes..

Keep your november revenue in a separate table.

In your new November column, type:

=IF(iserror(VLOOKUP(A2,G:H,2,0),"",VLOOKUP(A2,G:H, 2,0))

This says you are looking for the value in cell A2 (Jones Harbor LLC) in
your new table. I'm assuming here that the new table is in columns G and H.
Then, assuming the November value is in the 2nd column, I ask for the 2ngd
column to be returned. I only want the value returned if the names match
exactly, thus the ,0. If the company doesn't have a Novemebr value, return
"", a null cell.

Then, a 2nd step would be to do a countif(G2,,A:A). For any cell with a 0,
add this to the bottom of your master list as this is a new company in
November...

"blucajun" wrote:

I have a spreadsheet that I update monthly to track sales by company. I
originally got the format from a former co-worker and couldn't figure out if
he maintained it manually or if there was a more automated way of updating it
each month. It's basically set up like this:

Column A = Company Name
Column B = September Revenue
Column C = October Revenue
Column D = November Revenue
etc.

There are hundreds of rows of individual companies and each month I have to
add a new column for the month's revenues. What I've been doing is pasting
in the newest month's list of revenue by company (for instance, November) to
the end of the data already in the spreadsheet, moving the list of new
revenue over to the November column, and then sorting everything by company
name (col. A). Many of the companies are existing clients that buy each
month so I end up with:
Company Sep Oct Nov
Row 1 = Jones Harbor LLC $200 $200
Row 2 = Jones Harbor LLC $200
Row 3 = English Brakes Inc. $175 $900
Row 4 = English Brakes Inc. $350
Row 5 = Greene & Sons $550
etc.

I have to manually move the revenue in the Nov. column up one row to the row
containing the continuous data. (i.e., I'd move the $200 in cell D2 up to D1
and D4 up to D3.) I'd prefer not doing it manually as it takes forever and
leaves room for error. Is this even possible?