SKU auto creation

[handbaked]

Hi everyone

I am new to this forum but have a question I hope someone can help me with.

Firstly, I am not an advanced user on Excel

I have a very large CSV file that contains an inventory but I need to assign an SKU to every line in the CSV. This SKU needs to be a set of numbers but with the last 2 numbers/letters relevant to information contained on the same line in the csv (but in 2 different columns).

So as an example by example column D contains information regarding what is called a Status. This column either says 'New', 'Complete in Box', 'Boxed', 'Cart/CD Only' or 'Misc'. I would like to assign a number to each of these so that the last number in the SKU is 1, 2, 3, 4 or 5.

The CSV also includes another column called 'Condition'. This either shows as 'New', 'Very Good', 'Good' or 'Acceptable'. I would like to assign a letter to each of these so that the 2nd to last digit/letter in the SKU is either A, B, C or D.

How would I go about auto creating the SKU's based on this information?

Thanks in advance!

 

[sijpie]

The easiest to understand what you are doing is to work with a few helper columns. You don't mention how the main number of the SKU is made up, so in the below example I have assumed it is an incremental number.

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
3	X	Y	Z	Status	A	B	C	D	Condition	F	G		SKU Nr	Cond Code	Stat Code	SKU
4	abcd1	abcd4	abcd5	New	abcd8	abcd1	abcd4	abcd5	New	abcd8	abcd1		133332	A	1	133332A1
5	abcd2	abcd5	abcd6	Complete in Box	abcd9	abcd2	abcd5	abcd6	Very Good	abcd9	abcd2		166665	B	2	166665B2
6	abcd3	abcd6	abcd7	Boxed	abcd10	abcd3	abcd6	abcd7	Good	abcd10	abcd3		199998	C	3	199998C3
7	abcd4	abcd7	abcd8	Cart/CD Only	abcd11	abcd4	abcd7	abcd8	Acceptable	abcd11	abcd4		233331	D	4	233331D4
8	abcd5	abcd8	abcd9	New	abcd12	abcd5	abcd8	abcd9	Very Good	abcd12	abcd5		266664	B	1	266664B1
9	abcd6	abcd9	abcd10	Complete in Box	abcd13	abcd6	abcd9	abcd10	Good	abcd13	abcd6		299997	C	2	299997C2
10	abcd7	abcd10	abcd11	Boxed	abcd14	abcd7	abcd10	abcd11	New	abcd14	abcd7		333330	A	3	333330A3
11	abcd8	abcd11	abcd12	Cart/CD Only	abcd15	abcd8	abcd11	abcd12	Very Good	abcd15	abcd8		366663	B	4	366663B4
12	abcd9	abcd12	abcd13	Boxed	abcd16	abcd9	abcd12	abcd13	Good	abcd16	abcd9		399996	C	3	399996C3
13	abcd10	abcd13	abcd14	Cart/CD Only	abcd17	abcd10	abcd13	abcd14	Acceptable	abcd17	abcd10		433329	D	4	433329D4
SKU

Cell Formulas
Range	Formula
M4	=ROW()*33333
N4	=IF(I4="New","A",IF(I4="Very Good","B",IF(I4="Good","C",IF(I4="Acceptable","D","Err"))))
O4	=IF(D4="New",1,IF(D4="Complete in Box",2,IF(D4="Boxed",3,IF(D4="Cart/CD Only",4,"Err"))))
P4	=M4&N4&O4

 

[handbaked]

Thanks so much for the prompt response, that has worked a treat! I can now understand how it is done and I can modify it accordingly.

 

[handbaked]

A question I hope you or someone else may be able to answer is how to also add the first 3 letters of text/data from a particular column to the SKU/further helper column?

Thanks in advance.

 

[sijpie]

use =Left(G6,3)
to get the first three letters of the text in G6