OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

comparing stock numbers

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
sparky85012
Newbie
Newbie


Joined: 21 May 2012
Posts: 4

PostPosted: Mon May 21, 2012 1:00 pm    Post subject: comparing stock numbers Reply with quote

Hello:
My supplier sends out a nightly snapshot of all of his current stock, quantity changes, etc. in a csv file.
While I do not carry all of his products, I do carry about 1,000 of the 2,600+ available.

I have my current stock numbers in column A and his (via cut and paste) in column B
A
prod115
prod120
prod132
and so on

B
prod001
prod002
prod003
and so on

Does anyone know of a way to "sieve" out the unwanted data?

My apologies if this has been asked before.

TIA, Sparky
Back to top
View user's profile Send private message
UmTheMuse
General User
General User


Joined: 05 Apr 2012
Posts: 33
Location: United States

PostPosted: Mon May 21, 2012 3:09 pm    Post subject: Reply with quote

I can't quite tell what info you want to sieve out, but hopefully the filter tool is what you're looking for. Click on a cell in column A or B. Go to the menu at top and click on Data. Go down to Filter->Standard Filter and choose your criteria.

If that's not what you want, maybe you could sort it and just find it yourself. The sort function is just above the filter tool.
Back to top
View user's profile Send private message
sparky85012
Newbie
Newbie


Joined: 21 May 2012
Posts: 4

PostPosted: Mon May 21, 2012 4:28 pm    Post subject: Reply with quote

UmTheMuse wrote:
I can't quite tell what info you want to sieve out, but hopefully the filter tool is what you're looking for. Click on a cell in column A or B. Go to the menu at top and click on Data. Go down to Filter->Standard Filter and choose your criteria.

If that's not what you want, maybe you could sort it and just find it yourself. The sort function is just above the filter tool.


Thanks for your fast reply!!!

What I'm hoping to do is get rid of the content of column B that does not match the content of Column A, so that I am left with just the stock numbers that correspond. An example would be get rid of all the data in B until it matches A, then repeat until it reaches the next number in A and so on.

Again, thank you for your help.
Sparky
Back to top
View user's profile Send private message
ozzie
OOo Advocate
OOo Advocate


Joined: 29 Jul 2010
Posts: 400
Location: victoria

PostPosted: Mon May 21, 2012 8:44 pm    Post subject: Reply with quote

Or perhaps you want something like this-
Code:
=VLOOKUP(A2;'file:///C:/Users/Name/Documents/daily.csv'#$Sheet1.$A$1:$G$2602;2;0)

If this is in B2 and your list of products are in A2->A1002 it will
look for your product item in an alphabetically sorted list in file "daily.csv" and grab the figure after the first comma without your even needing to open that suppliers file.
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
scsisys
OOo Advocate
OOo Advocate


Joined: 17 Dec 2009
Posts: 248

PostPosted: Mon May 21, 2012 9:00 pm    Post subject: Reply with quote

sparky85012.....

One option is to use an INDEX / MATCH formula and a helper column(s) .

Assuming A1 and columns to the right are header rows, enter the formula below in C2.
Replace the B$xxxx with the last row number containing data for column B and then copy down to the last row in which Col. A has data .

=IF(ISNA(INDEX(B$2:b$xxxx;MATCH(A2;B$2:b$xxxx;0)));"";INDEX(B$2:b$xxxx;MATCH(A2;B$2:b$xxxx;0)))


What you should end up with in Col. C is a list of matches to YOUR stock.

scsisys
_________________
OO 3.2.1
Win XP /SP3
Back to top
View user's profile Send private message
sparky85012
Newbie
Newbie


Joined: 21 May 2012
Posts: 4

PostPosted: Tue May 22, 2012 5:21 am    Post subject: Reply with quote

ozzie wrote:
Or perhaps you want something like this-
Code:
=VLOOKUP(A2;'file:///C:/Users/Name/Documents/daily.csv'#$Sheet1.$A$1:$G$2602;2;0)

If this is in B2 and your list of products are in A2->A1002 it will
look for your product item in an alphabetically sorted list in file "daily.csv" and grab the figure after the first comma without your even needing to open that suppliers file.


Thanks! I'll try that scenario when I get home this afternoon/evening.
Sparky85012
Back to top
View user's profile Send private message
sparky85012
Newbie
Newbie


Joined: 21 May 2012
Posts: 4

PostPosted: Tue May 22, 2012 5:23 am    Post subject: Reply with quote

scsisys wrote:
sparky85012.....

One option is to use an INDEX / MATCH formula and a helper column(s) .

Assuming A1 and columns to the right are header rows, enter the formula below in C2.
Replace the B$xxxx with the last row number containing data for column B and then copy down to the last row in which Col. A has data .

=IF(ISNA(INDEX(B$2:b$xxxx;MATCH(A2;B$2:b$xxxx;0)));"";INDEX(B$2:b$xxxx;MATCH(A2;B$2:b$xxxx;0)))


What you should end up with in Col. C is a list of matches to YOUR stock.

scsisys


Thanks! I'll try that scenario when I get home this afternoon/evening.
Sparky85012
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group