Vlookup question - updating GL Account information shared from UNSPSC codes

[goodmachine]

Problem: I have a spreadsheet that contains UNSPSC codes or commodity codes that are mapped to GL Accounts. I need to update the UNSPSC codes to the new GL accounts from an updated sheet provided from finance.

1) First thing I need to do is add the text "SHARE" as a prefix in front of the UNSPSC codes on the sheet provided from finance- how can I do that?

For example: 123456, needs to appear as SHARE:123456

Once I complete, I can begin work on updating the GL Accounts on the spreadsheet that I own.

2) Column A in the finance sheet contains the newly updated UNSPC codes that now have the "SHARE" in front of the UNSPSC codes.

Column G in the finance sheet contains the new GL Accounts.


I need to update the GL Accounts in Column A in the sheet I own from the values in Column G on the Finance Sheet.

For example: I need to update the 677010 in My Sheet to 699080 based on the Commodity Code

Finance Sheet
[TABLE="width: 500"]
<tbody>[TR]
[TD]Commodity Code[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]New Account[/TD]
[/TR]
[TR]
[TD]SHARE:4014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]699080[/TD]
[/TR]
</tbody>[/TABLE]


My Sheet

[TABLE="width: 250"]
<tbody>[TR]
[TD]Account[/TD]
[TD][/TD]
[TD]Common ID[/TD]
[/TR]
[TR]
[TD]SHARE:677010[/TD]
[TD][/TD]
[TD]4014[/TD]
[/TR]
</tbody>[/TABLE]


The data that ties these two separate sheets are the UNSPSC codes. I'm assuming this would have to be done with VLOOKUP, but my skills are not that great. Also, I am using Excel 2007.

Any help would be greatly appreciated.

Thanks!

 

[ajm]

Problem: I have a spreadsheet that contains UNSPSC codes or commodity codes that are mapped to GL Accounts. I need to update the UNSPSC codes to the new GL accounts from an updated sheet provided from finance.

1) First thing I need to do is add the text "SHARE" as a prefix in front of the UNSPSC codes on the sheet provided from finance- how can I do that?

For example: 123456, needs to appear as SHARE:123456

Once I complete, I can begin work on updating the GL Accounts on the spreadsheet that I own.

hmmm, UNSPSC. now you are talking. this is my end of town.

this first one is simple enough. I will try an answer your other queries also, bit by bit.

so, you could either insert a column to the left of the UNSPSC codes or pick a free column to the right of the table. Either way, assuming you have a heading in the first row, simply (i will use the insert method where my inserted column is A), pop in this formula in A2: ="SHARE:"&B2

Copy this down the page and you will end up with a list of codes that look exactly as you want.

Next step: you can either copy these and paste as values over the original codes, effectively replacing 123456 with SHARE:123456 or you can keep the originals and your new ones so that you can do any lookups later, if needed.

thats for starters

 

[ajm]

2) Column A in the finance sheet contains the newly updated UNSPC codes that now have the "SHARE" in front of the UNSPSC codes.

Column G in the finance sheet contains the new GL Accounts.


I need to update the GL Accounts in Column A in the sheet I own from the values in Column G on the Finance Sheet.

For example: I need to update the 677010 in My Sheet to 699080 based on the Commodity Code

Finance Sheet
[TABLE="width: 500"]
<tbody>[TR]
[TD]Commodity Code[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]New Account[/TD]
[/TR]
[TR]
[TD]SHARE:4014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]699080[/TD]
[/TR]
</tbody>[/TABLE]


My Sheet

[TABLE="width: 250"]
<tbody>[TR]
[TD]Account[/TD]
[TD][/TD]
[TD]Common ID[/TD]
[/TR]
[TR]
[TD]SHARE:677010[/TD]
[TD][/TD]
[TD]4014[/TD]
[/TR]
</tbody>[/TABLE]


The data that ties these two separate sheets are the UNSPSC codes. I'm assuming this would have to be done with VLOOKUP, but my skills are not that great. Also, I am using Excel 2007.

Any help would be greatly appreciated.

Thanks!

on to query 2:

on your sheet, you will need 2 columns to the right of your existing used range. so, say your last used column is Z, use AA and AB.

into the first new column, at the top of your list, say AA2 put in this formula "=VLOOKUP("SHARE:"&H1,Sheet1!A1:G1,7,0)". Change H1 to whichever column holds the common ID and the Sheet1!A1:G1 for the sheet containing the finance list of codes. this formula adds SHARE: to the front of your common ID to ensure that its searchable against the finance tables, then looks at the finance tables for this exact string. If you added SHARE to the common ID using my first response above, then just drop it here. the formula would then be "=VLOOKUP(H1,Sheet1!A1:G1,7,0)"when its found, it returns to AA2 on your sheet the value contained in G, 7 columns to the right of where its found. Remember that the first column in the range is included in this 7: so A - G is 7 columns not 6. The zero at the end of the vlookup formula ensures that the lookup value is an exact match for what you are looking for. That is, it finds SHARE:1234 not SHARE:123 or anything else.

so, in AA2 you now have the new GL code. If you then use the method i described initially, add Share to this new GL code in the next column AB2. formula is ="SHARE:"&AA2. Copy this result, once you have done the entire column, and paste as values over your existing Account and then you will have the new GL associated with your codes. (SHARE:677010>>SHARE:699080)

come back to me if i have totally confused you. I sent you a PM too, BTW