Retrieve specific date from one row of one column that is changing

How do I pull a specific cost center number from column B to line up with an
account also in column B. For example I have a list of accounts followed by
the cost center that I need to tie to the account associated with the cost
center. The number of accounts and cost centers are changing as I go down
through column B.

Example:
Column A Column B
51000004 CONSUMPTION-MAINTENANCE PARTS
71000000 MAINTENANCE
71000047 MAINTENANCE SERVICES
71080002 DEPRECIATION - PLANTS
71190070 I/C - PRINT SHOP CHARGES
598006 TNY - NB LINE #2
51000004 CONSUMPTION-MAINTENANCE PARTS
71000000 MAINTENANCE
71000047 MAINTENANCE SERVICES
71010000 SUPPLIES
71080002 DEPRECIATION - PLANTS
71080004 DEPRECIATION - OFFICE
598007 TNY - NB LINE #3
51000004 CONSUMPTION-MAINTENANCE PARTS
71000000 MAINTENANCE
71000047 MAINTENANCE SERVICES
71040002 MACHINE RENT
71080002 DEPRECIATION - PLANTS
71190040 PRODUCTION COSTS-OVERHEAD
598008 TNY - CHARLIE #6 BAKERY

The cost centers are the six digit numbers I would like to have the cost
center in column A next the the account.

Clear as mud I know,

lpdc

Can anybody help me with this? Or do you need further clarification?

"lpdc" wrote:

> How do I pull a specific cost center number from column B to line up with an
> account also in column B. For example I have a list of accounts followed by
> the cost center that I need to tie to the account associated with the cost
> center. The number of accounts and cost centers are changing as I go down
> through column B.
>
> Example:
> Column A Column B
> 51000004 CONSUMPTION-MAINTENANCE PARTS
> 71000000 MAINTENANCE
> 71000047 MAINTENANCE SERVICES
> 71080002 DEPRECIATION - PLANTS
> 71190070 I/C - PRINT SHOP CHARGES
> 598006 TNY - NB LINE #2
> 51000004 CONSUMPTION-MAINTENANCE PARTS
> 71000000 MAINTENANCE
> 71000047 MAINTENANCE SERVICES
> 71010000 SUPPLIES
> 71080002 DEPRECIATION - PLANTS
> 71080004 DEPRECIATION - OFFICE
> 598007 TNY - NB LINE #3
> 51000004 CONSUMPTION-MAINTENANCE PARTS
> 71000000 MAINTENANCE
> 71000047 MAINTENANCE SERVICES
> 71040002 MACHINE RENT
> 71080002 DEPRECIATION - PLANTS
> 71190040 PRODUCTION COSTS-OVERHEAD
> 598008 TNY - CHARLIE #6 BAKERY
>
> The cost centers are the six digit numbers I would like to have the cost
> center in column A next the the account.
>
> Clear as mud I know,
>
> lpdc
>

I inserted a new column A.

With your test data, the data went to row 20.
On the lastrow (I'm gonna use 20, so it's Cell A20), put this formula:
=b20
Then in one row up (A19), put this formula:
=IF(LEN(B19)=6,B19,A20)

And drag it up to the top of the column.

It looked like this when I was done:

598006 51000004 CONSUMPTION-MAINTENANCE PARTS
598006 71000000 MAINTENANCE
598006 71000047 MAINTENANCE SERVICES
598006 71080002 DEPRECIATION - PLANTS
598006 71190070 I/C - PRINT SHOP CHARGES
598006 598006 TNY - NB LINE #2
598007 51000004 CONSUMPTION-MAINTENANCE PARTS
598007 71000000 MAINTENANCE
598007 71000047 MAINTENANCE SERVICES
598007 71010000 SUPPLIES
598007 71080002 DEPRECIATION - PLANTS
598007 71080004 DEPRECIATION - OFFICE
598007 598007 TNY - NB LINE #3
598008 51000004 CONSUMPTION-MAINTENANCE PARTS
598008 71000000 MAINTENANCE
598008 71000047 MAINTENANCE SERVICES
598008 71040002 MACHINE RENT
598008 71080002 DEPRECIATION - PLANTS
598008 71190040 PRODUCTION COSTS-OVERHEAD
598008 598008 TNY - CHARLIE #6 BAKERY



lpdc wrote:
>
> How do I pull a specific cost center number from column B to line up with an
> account also in column B. For example I have a list of accounts followed by
> the cost center that I need to tie to the account associated with the cost
> center. The number of accounts and cost centers are changing as I go down
> through column B.
>
> Example:
> Column A Column B
> 51000004 CONSUMPTION-MAINTENANCE PARTS
> 71000000 MAINTENANCE
> 71000047 MAINTENANCE SERVICES
> 71080002 DEPRECIATION - PLANTS
> 71190070 I/C - PRINT SHOP CHARGES
> 598006 TNY - NB LINE #2
> 51000004 CONSUMPTION-MAINTENANCE PARTS
> 71000000 MAINTENANCE
> 71000047 MAINTENANCE SERVICES
> 71010000 SUPPLIES
> 71080002 DEPRECIATION - PLANTS
> 71080004 DEPRECIATION - OFFICE
> 598007 TNY - NB LINE #3
> 51000004 CONSUMPTION-MAINTENANCE PARTS
> 71000000 MAINTENANCE
> 71000047 MAINTENANCE SERVICES
> 71040002 MACHINE RENT
> 71080002 DEPRECIATION - PLANTS
> 71190040 PRODUCTION COSTS-OVERHEAD
> 598008 TNY - CHARLIE #6 BAKERY
>
> The cost centers are the six digit numbers I would like to have the cost
> center in column A next the the account.
>
> Clear as mud I know,
>
> lpdc

--

Dave Peterson