I have two Columns data
first column name is "Items Recieved" it contains 25000 values.
second column contains "Delivered Items" it contains 22000 values which are derived from above 2500 recieved values.
remaining 3000 values are Un-Delivered Items.
I want to fetch the data of these 3000 items. in third columns. please any idea ?
Last edited by syedfahadjalali; 03-07-2013 at 02:12 PM.
Column A contains All Values. Column B Contains Some Of Them . I want To Find And Write Those Values In Column C which Are Missing In Column B But Exist In Clumn A
Not sure of an easier method. I normally do in this manner.
1) Keep the lists in two sheets with the same column names.
2) Introduce a column "Available" by side of the first list
3) Use Advanced Filter
Range : Range of First List including the "Available" column
Criteria : Second List
4) Executing Advanced Filter will now display in first list items available in second list. Mark a flag in "Available" Column (Y)
5) Clear the filter -> Create standard filter for list1 and filter "Available" Column for blanks.
A Sample workbook attached for reference.
Nothing was in your exel file. only two same columns in two sheets with same values
Using a dummy column
in C2 =IF(ISNUMBER(MATCH(A2, $B$2:$B$2000,0)), C1+1, C1) copied down
In D2
=IF(ROW(A1)>MAX(C:C), "",INDEX($A$2:$A$2000, MATCH(ROW(A1), $C$2:$C$2000,0)))
Does that work for you?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Not worked
A B
22 77
33 200
200 55
20 77
55 88
66
77
88
99
i want to copy missing elements in C
the values which are missing in B but exist in A ????
Oops, had the one formula backwards
=IF(ISNUMBER(MATCH(A2, $B$2:$B$2000,0)), C1, C1+1)
See attached
thnkssssssssssssssssss it is working
I found this as already solved. But let me give clarification on your query on the Excel file attached. Sheet1 is in Advanced Filter mode. Please remove filter (Data -> Filter ->Click Filter to remove). You will see more items in Sheet1 than Sheet2. Then try to recreate the advanced filter using the steps provided.Originally Posted by syedfahadjalali
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks