| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help!!! Extracting a number to line up with a different number
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. The Cost Center is the six digit number in 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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help!!! Extracting a number to line up with a different number
If the column B only contains 8 digits or 6 digits codes, you can use a
formula like this on A1: =IF(LEN(B1)=8,A2,B1) And drag it accross the A column. Hope this helps, Miguel. "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. The Cost Center is the six digit number in 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help!!! Extracting a number to line up with a different number
Miguel,
Thanks for the prompt response. Will this line the Cost Center up on the same line as the Account number that is associated with. In the example below I would like the 8 digit account number to have the cost center number moved to column A and be just to the left of it of the account number. "Miguel Zapico" wrote: If the column B only contains 8 digits or 6 digits codes, you can use a formula like this on A1: =IF(LEN(B1)=8,A2,B1) And drag it accross the A column. Hope this helps, Miguel. "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. The Cost Center is the six digit number in 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help!!! Extracting a number to line up with a different number
This formula won't work if it is not dragged along the column, as it relies
on the value below the current one. The final result will be: Column A Column B Column C 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 But again, only if column B contains just the code. Was this what you needed? If not, please tell. Miguel. "lpdc" wrote: Miguel, Thanks for the prompt response. Will this line the Cost Center up on the same line as the Account number that is associated with. In the example below I would like the 8 digit account number to have the cost center number moved to column A and be just to the left of it of the account number. "Miguel Zapico" wrote: If the column B only contains 8 digits or 6 digits codes, you can use a formula like this on A1: =IF(LEN(B1)=8,A2,B1) And drag it accross the A column. Hope this helps, Miguel. "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. The Cost Center is the six digit number in 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help!!! Extracting a number to line up with a different number
Miguel,
Your a genius it worked perfectly, Thanks, Again "Miguel Zapico" wrote: This formula won't work if it is not dragged along the column, as it relies on the value below the current one. The final result will be: Column A Column B Column C 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 But again, only if column B contains just the code. Was this what you needed? If not, please tell. Miguel. "lpdc" wrote: Miguel, Thanks for the prompt response. Will this line the Cost Center up on the same line as the Account number that is associated with. In the example below I would like the 8 digit account number to have the cost center number moved to column A and be just to the left of it of the account number. "Miguel Zapico" wrote: If the column B only contains 8 digits or 6 digits codes, you can use a formula like this on A1: =IF(LEN(B1)=8,A2,B1) And drag it accross the A column. Hope this helps, Miguel. "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. The Cost Center is the six digit number in 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vlookup returns line number | Excel Worksheet Functions | |||
| Extracting a number from a string and converting it into an Intege | Excel Worksheet Functions | |||
| vlookup with more than number to be retrieved | Excel Worksheet Functions | |||
| How to format a number in Indian style in Excel? | Excel Discussion (Misc queries) | |||
| How can I asign a number value to a text line in Excel? | Charts and Charting in Excel | |||
Linear Mode
