Importing data

[AB]

I have over 12 thousand prices in a single column. They look like this.

595000
100250
150099
859900


595000 is supposed to be $5.95
100250 is supposed to be $100.25
150099 is supposed to be $1500.99
859900 is supposed to be $85.99

Help, how do I convert this to the correct amounts.


Thankx in advance
A.B. Smith
http://www.onlinepowersports.com

 

[KenCriss]

Remember, Nate said the function "...assumes that the last non-zero is the last number in the returned dollar amount".

 

[NateO]

Hello again,

3.89 should be 38.90......3.25 should be 38.90, many of the others work depending on where the zero is.

Which zero? Your data points had many zeros in them.

What is the logic? I created some logic based on your data sample, you're saying that doesn't work. What's the conversion logic and I'm sure someone here can help.

 

[AB]

a portion of my data

a copied portion of the file in question.xls
	A	B	C	D	E	F	G
1	partnumb	description	cost	msrp
2	AAK-H03-002	A-ARMKIT,TRX250R86	38900	59950	KIT	0430D	13
3	AAK-S06-400	A-ARMKIT,LOWER,LTZ400	389000	59950	KIT	0430D	6
4	AAK-S07-400	A-ARMKIT,UPPER,LTZ400	325000	49950	KIT	0430D	6
5	AAL-0043	F.C.SHOCKSPRING4.3KG	682000	104950	EA	1312F	40
6	AAL-0045	F.C.SHOCKSPRING4.5KG	682000	104950	EA	1312F	40
7	AAL-0046	F.C.SHOCKSPRING,4.6KG	682000	104950	EA	1312F	43
8	AAL-0047	F.C.SHOCKSPRING4.7KG	682000	104950	EA	1312F	45
9	AAL-0048	F.C.SHOCKSPRING,4.8KG	682000	104950	EA	1312F	43
10	AAL-0049	F.C.SHOCKSPRING4.9KG	682000	104950	EA	1312F	43
11	AAL-0050	F.C.SHOCKSPRING,5.0KG	682000	104950	EA	1312F	43
12	AAL-0051	F.C.SHOCKSPRING5.1KG	682000	104950	EA	1312F	47
13	AAL-0052	F.C.SHOCKSPRING,5.2KG	682000	104950	EA	1312F	43
14	AAL-0053	F.C.SHOCKSPRING5.3KG	682000	104950	EA	1312F	46
15	AAL-0054	F.C.SHOCKSPRING,5.4KG	682000	104950	EA	1312F	43
16	AAL-0055	F.C.SHOCKSPRING5.5KG	682000	104950	EA	1312F	46
17	AAL-0056	F.C.SHOCKSPRING,5.6KG	682000	104950	EA	1312F	46
Sheet1

 

[AB]

Here is what your suggestion did for me. 3.89 should be 38.90

PART01.xls
	C	D	E	F	G	H	I
2	A-ARMKIT,TRX250R86	38900	3.89	5995	59.95	KIT	0430D
3	A-ARMKIT,LOWER,LTZ400	38900	3.89	5995	59.95	KIT	0430D
4	A-ARMKIT,UPPER,LTZ400	32500	3.25	4995	49.95	KIT	0430D
5	F.C.SHOCKSPRING4.3KG	68200	6.82	10495	104.95	EA	1312F
6	F.C.SHOCKSPRING4.5KG	68200	6.82	10495	104.95	EA	1312F
7	F.C.SHOCKSPRING,4.6KG	68200	6.82	10495	104.95	EA	1312F
8	F.C.SHOCKSPRING4.7KG	68200	6.82	10495	104.95	EA	1312F
9	F.C.SHOCKSPRING,4.8KG	68200	6.82	10495	104.95	EA	1312F
10	F.C.SHOCKSPRING4.9KG	68200	6.82	10495	104.95	EA	1312F
11	F.C.SHOCKSPRING,5.0KG	68200	6.82	10495	104.95	EA	1312F
12	F.C.SHOCKSPRING5.1KG	68200	6.82	10495	104.95	EA	1312F
13	F.C.SHOCKSPRING,5.2KG	68200	6.82	10495	104.95	EA	1312F
14	F.C.SHOCKSPRING5.3KG	68200	6.82	10495	104.95	EA	1312F
15	F.C.SHOCKSPRING,5.4KG	68200	6.82	10495	104.95	EA	1312F
16	F.C.SHOCKSPRING5.5KG	68200	6.82	10495	104.95	EA	1312F
17	F.C.SHOCKSPRING,5.6KG	68200	6.82	10495	104.95	EA	1312F
18	F.C.SHOCKSPRING5.7KG	68200	6.82	10495	104.95	EA	1312F
19	F.C.SHOCKSPRING5.9KG	68200	6.82	10495	104.95	EA	1312F
20	F.C.SHOCKSPRING6.1KG	68200	6.82	10495	104.95	EA	1312F
PART01

 

[NateO]

Right, no surprise as the algorithm was written to do just that, based on your original sample in this thread; that is, it looks for the last non-zero number and assumes that's the cent, then moves left.

What I think you want to do at this point is explain in plain English when a number should be 38.90 instead of 3.89 (just an example) because I can't tell/follow the logic from your data dumps...

Also, see the HTML maker FAQ For posting Spreadsheets to the board:

http://www.mrexcel.com/board2/viewtopic.php?t=92622#problems

 

[AB]

Ok, lets try again, I have a set of numbers given to me by the supplier. Here are the examples.

38900 needs to become 38.90
5995 needs to become 59.95
3250 needs to become 32.50
6820 needs to become 68.20
150700 needs to become 150.70
9500 needs to become 9.50
4550 needs to become 4.55
25990 needs to become 259.90
9490 needs to become 9.49

I hope this helps
A.B.

 

[NateO]

Well, your zeros aren't following a pattern, that I can see, which is how I originally thought I would construct a function...

You say it needs to become x, explain how you know it needs to be that; that's the logic we'll rewrite.

 

[AB]

Your question
explain how you know it needs to be that?

I have two rows of data, one showing cost (column d above in an earlier response) and one showing msrp (column f above in an earlier response)

The routine you gave me works correctly in column f. This allows me to look at the correct msrp and identify if the cost column is correct. If I see a msrp of 59.95 and a cost of 3.89 I know this 3.89 is not correct a the markup is too much.

What if I just sent you the text file I am trying to import/convert, you may be able to do something with the import routine. Right now I am am prompted by the excell text import wizard when I open the text file from the manufacturer. I set the arrows and get them in the best place I know how.

Here is a file format as received from the manufacturer. My problems are coming between positions 54 and 66.

PRICELIST FILE FORMAT
The following is the format for the files contained on these
diskettes, they are fixed record sizes (104) delimited by a
<cr> and <lf>.

LOCATION NAME DESCRIPTION
________ _____________ _____________________________________
1- 12 Part No. Compressed, no punctuation
13- 28 Part No. with punctuation
29- 53 Description Parts Description
54- 60 Unit Price 9999v999 where 'v' is implied decimal
61- 66 Sugg. Retail Suggested Retail Price 9999v99
67- 70 Qty Price Brk #1 9999 qty necessary for qty price #1
71- 74 ' ' ' #2 ' ' ' ' #2
75- 81 Qty Price #1 9999v999 (3 implied decimals)
82- 88 ' ' #2
89- 92 Unit of Meas. ie, EACH for each, etc
93- 96 Comm COde Commidity COde
97 Product Code Product Code
98 Drag Group 1 = Honda, 2 = Harley
3 = Common, 4 = Misc
99 Type Flag If this field is an '*' then
this part will not be availabe
after current inventories exhausted
100-104 Weight Weight
If you want to extract Drag Specialties only, use the
parts that have a 1, 2, 3 or 4 in the Drag Group (pos. 98).


I hope this helps.
A.B.