Hello,
This is an excel problem that I really need help with for my work. Thank you very much for your help. I placed entirely what I'm trying to do in this post. I don't really know how to do any of this and don't know if this is the correct approach. Please forgive me if this wasn't written as well as it could have been. Basically I'd like to know how to do what I've written below.
I'm working with two different spreadsheets. One spreadsheet is mine that I manage my inventory on. The other spreadsheet comes from a company that I want to sell items to using excel. What I want to do is use excel to place the quantity of items that I'd like to sell to the company on their spreadsheet and create a column on my spreadsheet with the updated quantity. These spreadsheets don't have any columns with cells that can be matched exactly between the two spreadsheets. There are parts of one column in my spreadsheet that are similar to a column in the companies spreadsheet. I think creating new columns in the spreadsheets and then using an "if this contains this then this" function would match nearly 100% of the items. Below I copied small examples of the two spreadsheets and included the steps that I think would work below that as well. Also, these spreadsheets do contain different amounts of items. Thank you for your time and help.
This is an example of my spreadsheet, the columns to the left column K are what I would start with and everything to the right of that is what I would like to make with excel:
<tbody>
</tbody>
This is an example of the spreadsheet from the company that I'd like to sell to, columns A through G are given by the company, anything to the right are additional columns that I added that I'd like to fill using excel/functions, for the example I filled those with what I'd like them to be manually:
<tbody>
</tbody>
Below are the technical steps that I thought might work for what I'm trying to do, for my example below I referred to each column by the letter code.
Step 1 and 2: Open my spreadsheet and the companies spreadsheet.
Step 3:
I would like to have one spreadsheet/workbook that I can Copy the companies spreadsheet and my inventory spreadsheet into, I think 2 separate tabs of a workbook for that that already has the necessary functions save in it.
Step 4:
For my example spreadsheet I used a sample of my inventory spreadsheet, which filled columns A through J.
Filling column K of my spreadsheet, “Set Code”
K2 = the Set code from A2(please refer to the example)
Filling column L of my spreadsheet “Rarity”
L2 = the Rarity from A2(please refer to the example)
Filling column M of my spreadsheet “Edition”
M2 = the Edition from A2(please refer to the example)
Step 5:
Filling column N of my spreadsheet “1st or no”
N2 = “1st" if M2 contains “1st" or “no" if M2 does not contain "1st"
Step 6:
Filling column O of my spreadsheet “Rarity 2”
O2 = The first word of L2
Step 7:
Filling Column P of my spreadsheet “Rarity Final”
P2 = The word from O2 unless O2 = “Rare” or if K2 Contains “DT0” and O2 = “Common". When O2 = “Rare” then P2 = “ - Rare”. When K2 contains “DT0” and O2 = “Common” then P2 = “Normal”
Step 8:
Using the companies spreadsheet.
Columns A through G were filled by by the company.
Filling Column H of the companies spreadsheet “1st or no”
H2 = “1st” when D2 of companies spreadsheet contains “1st” or “no” when D2 of companies spreadsheet does not contain “1st”.
Step 9:
Filling Columns I, J, and K of the companies spreadsheet spreadsheet.
If a cell in the companies spreadsheet column C’s row contains my spreadsheet's K2 and P2 and the same row of the companies spreadsheet’s column H is = to my spreadsheet's N2 then still in the same row of the companies spreadsheet fill column I, J and K with my spreadsheet’s A2, D2, and G2 respectively.
Step 10:
Filling the companies spreadsheet's column L
If the companies K2 is = to less than 1 then the companies L2 = .02. If the companies K2 is = to 1 or between 1 and 4.99 then the companies L2 = 60% of the companies K2. If the companies K2 is = to 5 or between 5 and 49.99 then the companies L2= 70% of the companies K2. If the companies K2 is = to or greater than 50 then the companies L2 = 80% of the companies K2.
Step 11:
Filling the companies column G
If the companies L2 is less than or = to the companies E2 then the companies G2 is to be = to the companies J2 when the companies J2 is less than or equal to the companies F2 or if the companies L2 is still less than or = to the companies E2 and the companies J2 is greater than the companies F2 then the companies G2 = the companies F2
Step 12:
Determining my spreadsheets column Q “Sell Qty”
If the companies I2 is = to one of the cells in the rows of my spreadsheet’s Column A then the same row of my spreadsheet’s column Q is to be = to the companies G2
Step 13:
Filling column R of my spreadsheet.
my spreadsheet's R2 is = my spreadsheet's D2 minus my spreadsheet's Q2
Step 14:
I would like to apply all of these steps to each of my the rows in my spreadsheet and the companies spreadsheet.
Step 15:
Generating 3 new spreadsheets possibly in new tabs of the same workbook.
Spreadsheet 1:
A spreadsheet of the companies finalized columns A through G, for making the sale to them.
Spreadsheet 2:
A spreadsheet of my original data my spreadsheets columns A through J with an additional "new quantities" column from my original spreadsheet.
Spreadsheet 3:
A pull sheet for pulling the items from my inventory that were sold, a spreadsheet that contains my spreadsheets "Product name" column,my spreadsheets "Sell Price" column, and the quantity being sold.
This is essentially what I'm trying to do. Thank you for your time and help, it would make it possible for me to do this. I hope it isn't a problem that I didn't limit this to specific questions.
Thanks sincerely
This is an excel problem that I really need help with for my work. Thank you very much for your help. I placed entirely what I'm trying to do in this post. I don't really know how to do any of this and don't know if this is the correct approach. Please forgive me if this wasn't written as well as it could have been. Basically I'd like to know how to do what I've written below.
I'm working with two different spreadsheets. One spreadsheet is mine that I manage my inventory on. The other spreadsheet comes from a company that I want to sell items to using excel. What I want to do is use excel to place the quantity of items that I'd like to sell to the company on their spreadsheet and create a column on my spreadsheet with the updated quantity. These spreadsheets don't have any columns with cells that can be matched exactly between the two spreadsheets. There are parts of one column in my spreadsheet that are similar to a column in the companies spreadsheet. I think creating new columns in the spreadsheets and then using an "if this contains this then this" function would match nearly 100% of the items. Below I copied small examples of the two spreadsheets and included the steps that I think would work below that as well. Also, these spreadsheets do contain different amounts of items. Thank you for your time and help.
This is an example of my spreadsheet, the columns to the left column K are what I would start with and everything to the right of that is what I would like to make with excel:
| Product Name(A) | Category(B) | Variant Info(C) | Qty(D) | Opt Qty(E) | Buy Price(F) | Sell Price(G) | URL(H) | Barcode(I) | Manufacturer SKU(J) | Set Code(K) | Rarity(L) | Edition(M) | 1st or no(N) | Rarity 2(O) | Rarity Final(P) | Sell Qty(Q) | New Qty(R) |
| Pot of Benevolence - ABPF-EN061 - Common - 1st Edition | Absolute Powerforce | Condition: Near Mint | 5 | 0 | 0 | 0.5 | url | ABPF-EN061 | Common | 1st Edition | 1st | Common | Common | TBD | Qty - Sell Qty | ||
| Pot of Benevolence - ABPF-EN061 - Common - Unlimited Edition | Absolute Powerforce | Condition: Near Mint | 5 | 0 | 0 | 0.5 | url | ABPF-EN061 | Common | Unlimited Edition | no | Common | Common | TBD | |||
| Cyber Dragon Zwei - ABPF-EN035 - Rare - 1st Edition | Absolute Powerforce | Condition: Near Mint | 5 | 0 | 2 | 0.5 | url | ABPF-EN035 | Rare | 1st Edition | 1st | Rare | - Rare | TBD | |||
| Cyber Dragon Zwei - ABPF-EN035 - Rare - Unlimited Edition | Absolute Powerforce | Condition: Near Mint | 5 | 0 | 0 | 0.5 | url | ABPF-EN035 | Rare | Unlimited Edition | no | Rare | - Rare | TBD | |||
| Jinzo - BPT-011 - Secret Rare - Limited Edition | 2002-2003 Collectors Tins | Condition: Near Mint | 5 | 0 | 0 | 3 | url | NoBarcode | BPT-011 | Secret Rare | Limited | no | Secret | Secret | TBD | ||
| Amazoness Chain Master - DT07-EN004 - Common - Unlimited Edition | Duel Terminal 7 | Condition: Near Mint | 5 | 0 | 0.1 | 0.45 | url | DT07-EN004 | Common | Unlimited Edition | no | Common | Normal | TBD |
<tbody>
</tbody>
This is an example of the spreadsheet from the company that I'd like to sell to, columns A through G are given by the company, anything to the right are additional columns that I added that I'd like to fill using excel/functions, for the example I filled those with what I'd like them to be manually:
| Product Id(A) | Edition(B) | Product Name(C) | Condition(D) | Buy Price(E) | Buy Qty(F) | Sell Qty(G) | 1st or no(H) | My Product Name(I) | Qty(J) | Sell Price(K) | Min Sell Price(L) |
| 2977911 | Absolute Powerforce ABPF | Pot of Benevolence - ABPF-EN061 - Common | Near Mint 1st Edition English Yugioh Card | 0.28 | 1 | TBD | 1st | Pot of Benevolence - ABPF-EN061 - Common - 1st Edition | 5 | 0.5 | 0.02 |
| 2978023 | Absolute Powerforce ABPF | Pot of Benevolence - ABPF-EN061 - Common | Near Mint Unlimited English Yugioh Card | 0.26 | 3 | TBD | no | Pot of Benevolence - ABPF-EN061 - Common - Unlimited Edition | 5 | 0.5 | 0.02 |
| 2977997 | Absolute Powerforce ABPF | Cyber Dragon Zwei - ABPF-EN035 - Rare | Near Mint Unlimited English Yugioh Card | 0.12 | 3 | TBD | no | Cyber Dragon Zwei - ABPF-EN035 - Rare - 1st Edition | 5 | 0.5 | 0.02 |
| 2977885 | Absolute Powerforce ABPF | Cyber Dragon Zwei - ABPF-EN035 - Rare | Near Mint 1st Edition English Yugioh Card | 0.34 | 1 | TBD | 1st | Cyber Dragon Zwei - ABPF-EN035 - Rare - Unlimited Edition | 5 | 0.5 | 0.02 |
| 1700706 | Yu-Gi-Oh! Promo Cards | Jinzo - BPT-011 - Secret Rare | Near Mint English Yugioh Card | 3.3 | 11 | TBD | no | Jinzo - BPT-011 - Secret Rare - Limited Edition | 5 | 3 | 1.8 |
| 4240878 | Duel Terminal 7 DT07 | Amazoness Chain Master - DT07-EN004 - Normal Parallel Rare | Near Mint English Yugioh Card | 0.13 | 3 | TBD | no | Amazoness Chain Master - DT07-EN004 - Common - Unlimited Edition | 5 | 0.45 | 0.02 |
<tbody>
</tbody>
Below are the technical steps that I thought might work for what I'm trying to do, for my example below I referred to each column by the letter code.
Step 1 and 2: Open my spreadsheet and the companies spreadsheet.
Step 3:
I would like to have one spreadsheet/workbook that I can Copy the companies spreadsheet and my inventory spreadsheet into, I think 2 separate tabs of a workbook for that that already has the necessary functions save in it.
Step 4:
For my example spreadsheet I used a sample of my inventory spreadsheet, which filled columns A through J.
Filling column K of my spreadsheet, “Set Code”
K2 = the Set code from A2(please refer to the example)
Filling column L of my spreadsheet “Rarity”
L2 = the Rarity from A2(please refer to the example)
Filling column M of my spreadsheet “Edition”
M2 = the Edition from A2(please refer to the example)
Step 5:
Filling column N of my spreadsheet “1st or no”
N2 = “1st" if M2 contains “1st" or “no" if M2 does not contain "1st"
Step 6:
Filling column O of my spreadsheet “Rarity 2”
O2 = The first word of L2
Step 7:
Filling Column P of my spreadsheet “Rarity Final”
P2 = The word from O2 unless O2 = “Rare” or if K2 Contains “DT0” and O2 = “Common". When O2 = “Rare” then P2 = “ - Rare”. When K2 contains “DT0” and O2 = “Common” then P2 = “Normal”
Step 8:
Using the companies spreadsheet.
Columns A through G were filled by by the company.
Filling Column H of the companies spreadsheet “1st or no”
H2 = “1st” when D2 of companies spreadsheet contains “1st” or “no” when D2 of companies spreadsheet does not contain “1st”.
Step 9:
Filling Columns I, J, and K of the companies spreadsheet spreadsheet.
If a cell in the companies spreadsheet column C’s row contains my spreadsheet's K2 and P2 and the same row of the companies spreadsheet’s column H is = to my spreadsheet's N2 then still in the same row of the companies spreadsheet fill column I, J and K with my spreadsheet’s A2, D2, and G2 respectively.
Step 10:
Filling the companies spreadsheet's column L
If the companies K2 is = to less than 1 then the companies L2 = .02. If the companies K2 is = to 1 or between 1 and 4.99 then the companies L2 = 60% of the companies K2. If the companies K2 is = to 5 or between 5 and 49.99 then the companies L2= 70% of the companies K2. If the companies K2 is = to or greater than 50 then the companies L2 = 80% of the companies K2.
Step 11:
Filling the companies column G
If the companies L2 is less than or = to the companies E2 then the companies G2 is to be = to the companies J2 when the companies J2 is less than or equal to the companies F2 or if the companies L2 is still less than or = to the companies E2 and the companies J2 is greater than the companies F2 then the companies G2 = the companies F2
Step 12:
Determining my spreadsheets column Q “Sell Qty”
If the companies I2 is = to one of the cells in the rows of my spreadsheet’s Column A then the same row of my spreadsheet’s column Q is to be = to the companies G2
Step 13:
Filling column R of my spreadsheet.
my spreadsheet's R2 is = my spreadsheet's D2 minus my spreadsheet's Q2
Step 14:
I would like to apply all of these steps to each of my the rows in my spreadsheet and the companies spreadsheet.
Step 15:
Generating 3 new spreadsheets possibly in new tabs of the same workbook.
Spreadsheet 1:
A spreadsheet of the companies finalized columns A through G, for making the sale to them.
Spreadsheet 2:
A spreadsheet of my original data my spreadsheets columns A through J with an additional "new quantities" column from my original spreadsheet.
Spreadsheet 3:
A pull sheet for pulling the items from my inventory that were sold, a spreadsheet that contains my spreadsheets "Product name" column,my spreadsheets "Sell Price" column, and the quantity being sold.
This is essentially what I'm trying to do. Thank you for your time and help, it would make it possible for me to do this. I hope it isn't a problem that I didn't limit this to specific questions.
Thanks sincerely
