excel spread sheet matching 2 columns

wegotitugetit

wegotitugetit

On the hit list !!!
Suspended / Banned
Messages
7,151
Name
alex
Edit My Images
Yes
so ill try my best to explain
in a column A has x amount of part numbers in column b i will need to match part number to column A but in column B it my only have half the numbers in A and in different order is there a way to match the two columns together i must say again column A can not be moved in any way ie the numbers

so in theory
1 1
2 2
3 3
4
5 5
6
7 7

example above the one's missing have not been put into coumn b and that is how i need it to be shown
 
you would need a third column at least, i dont think you could not do it with two columns that are already populated with data, as your query would overwrite data, for example
If B5 = A1 then populate B1, (this is not code, this is simplified) this could overwrite what is already in that cell,
if you can use a third column i could work out a macro for you
 
third column is no problem i could have 5 columns lol

i need to work out stock issue's and this is easiest way so far but with over 20,000 items and 15,000 items in stock im getting thrown off

what i need to do is is match the item.s 15,000 the the items in no column A then once done i need to mark all the missing ones as 0 and the rest as 100 long story but i dont mind adding that part manually as i can add 0 in column x and what ever is in stock will be left blank then i jsut need to use the find and replace feature to add the rest in as 100 sounds simple but i know it is not

databases is a option i think but having time to sit there and manually working it out is a problem i jsut dont have time i have one day a week to work on this stuff
 
this is quite difficult
ok here's a starter for you to count the spaces left it will identify what's missing from column B against column A and populate column C with a yes if it finds a match

Example

Code: 
 A      B       C
1      234     yes
2      1       yes
3
4      345
5
6      2

So what it will tell you is the columns that do not return a yes do not exist in column B In this example 1 & 2 exist in column B
Copy this into C1 then drag down to the bottom of column to fill every C cell with the incremental formula

Code: 
=IF(ISNA(VLOOKUP(A1,$B$1:$B$600,1,FALSE)),"","Yes")

i'll try to improve it for you when i get chance.
 
Last edited:
Wish I'd spotted this yesterday. I like a challenge to my limited Excel skills! I'm sure Gr8Shot has it in hand. If you need any help give me a shout.
 
Code: 
=IF(ISNA(VLOOKUP(A1,$B$1:$B$600,1,FALSE)),"","Yes")

i'll try to improve it for you when i get chance.[/QUOTE]

The above looks good, possibly just replace "" with 0 and "yes" with 100.
 
As I understood the problem, col A has the full list of part numbers; col B has a list of part numbers which is a subset of col A, in a random order.

If the part number in col A appears in col B, put 100 in col x, if it doesn't put 0 in col X

Code: 
=IF(ISNA(MATCH(A2,B:B,0)),0,100)

:shrug:
 
You must log in or register to reply here.
Back
Top