Excel question

[Spexxvet]

Please help!

I have an Excel spreadsheet with 46,000 rows of information. It is a list of frames, most of which come in multiple sizes and colors. So in column A, there might be 2 to 10 rows with the same entry, with columns b and c listing the sizes and colors.

I want to wean column a so that there is no repetition. How do I eliminate the repeats in column a without manually deleting repeating rows?

Thanks for any help.

[Pie]

Try this [microsoft.com].

[Shawnee123]

I can't explain crap. Try Pie's link, or send me the spreadsheet and I'll fix it for you.

[Spexxvet]

Thanks, Pie, but I don't have Office at work, so I wasn't able to use that technique.

Thanks, Shaw. It was the paste special/value that I was missing. Your way worked like a charm! I didn't get to try it until this morning.

[Shawnee123]

I re-read it and it sounded all wrong...but yeah, paste special/values turns your formula back into sortable values. I can figure excel stuff out, when I'm working through it, but I sure can't explain it. Glad you caught my post before I deleted it.

[Pie]

Originally Posted by Spexxvet

Thanks, Pie, but I don't have Office at work, so I wasn't able to use that technique.

Excel is part of Office, no?

[Spexxvet]

Originally Posted by Pie

Excel is part of Office, no?

Yeah, but here at Dr. Archaic's office, we have a version that's so old it was chiseled on stone tablets. No office.

[SteveDallas]

Originally Posted by Spexxvet

Please help!

I have an Excel spreadsheet with 46,000 rows of information.

Bwahahahahaha

Sorry. There might be more efficient ways to store this info.. something to think about for the future....

It sounds like you fixed it, so good for you! But I'm still confused. What good does it do to nuke values from Column A if those rows still need to stay in, because you have different sizes and colors?

[Spexxvet]

Originally Posted by SteveDallas

Bwahahahahaha

Sorry. There might be more efficient ways to store this info.. something to think about for the future....

It sounds like you fixed it, so good for you! But I'm still confused. What good does it do to nuke values from Column A if those rows still need to stay in, because you have different sizes and colors?

A company puts out a quarterly CD with ALL frame information on it. I import the frames collections that I carry into my Point-Of-Sale application. That's the efficient part.

When I imported all the collections, I included frames that I do not have in stock (foolish, in hindsight), and colors and sizes that I don't have in stock. That way I can easily answer when a customer asks "does this frame come larger, in red?"

Now, I want to generate a list of only the name of the frames that I have in stock, but my POS doesn't provide that report. So I exported the entire inventory list to an excel file (46,000 + records). I've culled the list down to 7,662 unique frame names. All I have to do now is print 187 pages, check off the ones I have in stock, go into my POS and delete the ones I don't have in stock, and then go into each record and input the retail price of the frame. I show about 900 frames, usually 2 colors in each frame, so that's about 450 frames, average 3.2 colors and 2.6 sizes (total estimate). Only about 3,700 records to edit. Oy!

[SteveDallas]

Can you export the entire list from the POS? Would in-stock items be differentiated in any way?

(We just acquired a bookstore--don't ask--and apparently the POS can't report total sales over a period of time. I have trouble believing this, but if I start asking too many questions I'll end up fixing it myself.)

Am I understanding that you export the quarterly CD into the POS every time? Does this overwrite the data in the POS? If so, it may be easier to clean up your Excel spreadsheet so you have only your stocked items and then dump that into the POS.

[Spexxvet]

Originally Posted by SteveDallas

Can you export the entire list from the POS?

Yes, that's what I did. I think that's all I can do.

Originally Posted by SteveDallas

Would in-stock items be differentiated in any way?

Yes, they would, or could have been. The POS performs in/out inventory functions. When I imported the frame information, I thought I was importing only the information. Actually, I was telling the POS that I received these frames, so enter them into inventory. As it stands, every frame appears to be in-stock.

This is a new process for me, and there wasn't any documentation. Previously, as I sold a frame that I'd never sold before, I would manually enter it into the POS, which was time consuming. I could have entered ALL my in-stock frames at one time (probably 1,800 frame) when we got the new POS in Jan 2004, but that seemed like wasted time, given that some of those frames would be exchanged before they'd be sold. The CD product is relatively new, so when I could get it for 25% off the normal $500/year, I decided to go for it. When I imported the collections that I felt would be useful, it entered every frame into my inventory, much to my surprise and frustration. Once I get this settled, as I recieve new frames into inventory, I can pop in the CD and import only those new frames.

Originally Posted by SteveDallas

Am I understanding that you export the quarterly CD into the POS every time?

I hope there is a way that I can import only changes, most inportantly cost and status (discontinued or active).

Originally Posted by SteveDallas

Does this overwrite the data in the POS?

I hope not.

Originally Posted by SteveDallas

If so, it may be easier to clean up your Excel spreadsheet so you have only your stocked items and then dump that into the POS.

I don't think I can dump the spreadsheet into the POS - I certainly don't know how.

[SteveDallas]

How does the CD info get into the POS? Unless the POS and the CD are from the same company, or two companies in cahoot, and it's some kind of garbled binary format intended to be completely useless... it's probably a flat-file text import. Comma-delimited is most popular, but there are other ways to do it. If you can get a look at the export file from the CD, the chances are pretty high that Excel can save a copy that duplicates the format.

The business about changes is an important issue that's often a pain in the ass. For example, I work with a web-based product that hosts online material for courses. Each course needs to have students enrolled in it. There's a procedure to import a list of students. But there's no automated way to get rid of students. So adding new students is OK (although it's stupid about some things--is it really a good idea to create two different accounts for the same email address? I personally don't think so.), but getting rid of ones who dropped the course remains a manual procedure.

[Spexxvet]

Originally Posted by SteveDallas

How does the CD info get into the POS? Unless the POS and the CD are from the same company, or two companies in cahoot,

Frames Data Quarterly provides the raw data. Each POS must have to write their own import program.

Originally Posted by SteveDallas

The business about changes is an important issue that's often a pain in the ass. For example, I work with a web-based product that hosts online material for courses. Each course needs to have students enrolled in it. There's a procedure to import a list of students. But there's no automated way to get rid of students. So adding new students is OK (although it's stupid about some things--is it really a good idea to create two different accounts for the same email address? I personally don't think so.), but getting rid of ones who dropped the course remains a manual procedure.

I have a feeling that much of this will also be manual. I'm not looking forward to it.

[Shawnee123]

Sorry guys, but everytime I read these posts I hear Piece of Shit rather than Point of Sale.

It's kind of funny if you replace all the POSs with Piece O'Shit.

For example:

Each POS must have to write their own import program.

[SteveDallas]

Originally Posted by Shawnee123

Sorry guys, but everytime I read these posts I hear Piece of Shit rather than Point of Sale.

I've decided this cannot possibly be an accident.