• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Auto delete the allocated serials.

R

realtop17

New Member
Hi all,


please help me on this scenario i'm using excel 2007


In excel WORK BOOK sheet1 title "fg monitoring" and sheet2 title "allocation"

fg monitoring is a soft copy wherein all information of finish goods are placed such as serials nos., p/n and so on.. and allocation is also a soft copy wherein finish goods are schedule to ship in different countries. see below format for your reference.


fg monitoring "sheet1"

s/n p/n

123 01

124 02

125 03

126 04

127 05

128 06

129 07


allocation "sheet2"


s/n p/n allocated to

123 01 china

124 02 saudi

125 03 china

126 04 saudi

127 05 china


My question what kind of formula to do automatic delete the serials in fg monitoring when i pasted the serials in aloocation sheet.


these are the out put i want in fg monitoring sheet.


fg monitoring "sheet1"

s/n p/n

128 06

129 07

the rest are deleted.

i wish you could help me!


Thank you,

realtop17
 
No formula can "delete" information, it can only provide some sort of output. We can start with a helper column to do that. If you really want to delete info, we can run a VB macro to quickly run through the data and delete all the lines.

Formula in fg monitoring sheet:

=SUMPRODUCT(1*((A3&B3)=(allocation!$A$1:$A$100&allocation!$B$1:$B$100)))>0

And here's the macro to run, that uses the helper column to find the rows to delete

[pre]
Code: 
Sub DeleteRecords()
Dim LastRow As Integer
Dim SearchRange As Range

'Which column is your helper column?
Set SearchRange = Range("C:C")

Application.ScreenUpdating = False
'Start looking for criteria
Set c = SearchRange.Find(What:="TRUE", After:=SearchRange.Cells(1, 1), LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
Do While Not c Is Nothing
c.EntireRow.Delete
Set c = SearchRange.FindNext(SearchRange.Cells(1, 1))
Loop
Application.ScreenUpdating = True
End Sub
[/pre]
 
how can i use the macro? i only do is to pasted the macro you give i press alt+F11 in VBA PROJECT SHEET1 (FG) but no happen. please help me how to use macro im macro beginner.
 
After you've got the helper column setup, then we can use the macro. In the Visual Basic editor, you'll need to go to Insert - Module, and paste the code there.


Then, in your workbook, go to the fg monitoring sheet, and then you can bring up the macro menu (Alt+F8) and run the macro. Be sure to change the helper column reference is needed.
 
Thank you luke now i have the idea i'll try 2mrw its getting late i'm going out now...

agin thank you very much...
 
Hi Luke,

i have a question what is helper column? please luke let me know...plssssssss....

[pre]
Code: 
Sub DeleteRecords()
Dim LastRow As Integer
Dim SearchRange As Range

'Which column is your helper column? luke please give me the example for helper column
Set SearchRange = Range("C:C")

Application.ScreenUpdating = False
'Start looking for criteria
Set c = SearchRange.Find(What:="TRUE", After:=SearchRange.Cells(1, 1), LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
Do While Not c Is Nothing
c.EntireRow.Delete
Set c = SearchRange.FindNext(SearchRange.Cells(1, 1))
Loop
Application.ScreenUpdating = True
End Sub
[/pre]
 
The helper column is whichever column you put the formula:

=SUMPRODUCT(1*((A3&B3)=(allocation!$A$1:$A$100&allocation!$B$1:$B$100)))>0


in. In the given code, I was assuming that you had put the formula in column C. If this is not that case, change the Range reference to something else, e.g. Range("D:D") if the formulas are in column D.
 
Now i know the helper column thank's for that bro!


i followed your sumproduct formula this is i got " N/A" i'll pasted the macro you gave i also delete the word "'Which column is your helper column?" and i rage the cell ("C:C") which is my actual helper column but nothing happened.


pleasee luke more patience.


thank you,

realtop17
 
HI Luke,


tnx i got it, when i pasted the serial in allocation sheet the result in monitoring sheet is "true" and it "false" when no same serial are pasted in monitoring sheet.


tnx bro!


follow up question.


insted of false can you do it fg and insted of true do it allocated.

i can not undo why?
 
hi luke,


luke thank you for helping me on this problem it realy works!


luke can you put the "fg" insted of false and "allocated" insted of true in helper column.


thank you,

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