| View previous topic :: View next topic |
| Author |
Message |
sparky85012
Newbie
Joined: 21 May 2012
Posts: 4
|
 Posted: Mon May 21, 2012 1:00 pm Post subject: comparing stock numbers |
 |
|
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 |
|
 |
UmTheMuse
General User
Joined: 05 Apr 2012
Posts: 33
Location: United States
|
| Posted: Mon May 21, 2012 3:09 pm Post subject: |
|
|
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 |
|
|
sparky85012
Newbie
Joined: 21 May 2012
Posts: 4
|
| Posted: Mon May 21, 2012 4:28 pm Post subject: |
|
|
| 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 |
|
|
ozzie
OOo Advocate
Joined: 29 Jul 2010
Posts: 316
Location: victoria
|
| Posted: Mon May 21, 2012 8:44 pm Post subject: |
|
|
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 |
|
|
scsisys
OOo Enthusiast
Joined: 17 Dec 2009
Posts: 163
|
| Posted: Mon May 21, 2012 9:00 pm Post subject: |
|
|
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 |
|
|
sparky85012
Newbie
Joined: 21 May 2012
Posts: 4
|
| Posted: Tue May 22, 2012 5:21 am Post subject: |
|
|
| 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 |
|
|
sparky85012
Newbie
Joined: 21 May 2012
Posts: 4
|
| Posted: Tue May 22, 2012 5:23 am Post subject: |
|
|
| 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 |
|
|
|
