EXCEL Experts Please help

[engr.fahad]

mujy tmam excel experts sy help ki zerorat hy .. main excel ki aik sheet bana chahta hn jis main yeh kam ho sakian agr mumkin ho to.

1- agr sheet 2 py koi data hy jiasy NIC #(313046778xxx00) to woi data jb main sheet 1 ya ya kisi or sheet py type kern to sheet to sy automatically delete ho jay.

2- koi aisa formula ho jo batay k sheet 2 py itna data remaining hy

3- koi aisa formula ho jo batay k sheet 1 py itna data enter ho chuka hy .


please agr aisa mumkin hy to kindlyyyyyyyyyyyyyyyyyyy help me

[syar]

salam::

1. Konsi shet delete ho jae. Sheet1 ya jis sheet par aapna data dobara lekhte hain. Agar sheet delete nahee karna tu konsa entry delete karne ha.
2. remaining data maloom karne k leye aap apni condition explain karain. K kis condition k tahat aap remaining data maloom karna chahte hain. Key remaining se murad empty rows aur column hain.
3. need same explanation as 2.

If possible provide small smaple.

[engr.fahad]

salam::

1. Konsi shet delete ho jae. Sheet1 ya jis sheet par aapna data dobara lekhte hain. Agar sheet delete nahee karna tu konsa entry delete karne ha.

WSLM::

[engr.fahad]

[QUOTE=syar;1526099]2. remaining data maloom karne k leye aap apni condition explain karain. K kis condition k tahat aap remaining data maloom karna chahte hain. Key remaining se murad empty rows aur column hain.
[QUOTE]

[engr.fahad]

3. need same explanation as 2.

[engr.fahad]

koiiiiiiiiiiiiii to reply kery g

[syar]

Sorry for late reply.

to delete the row
Alt+F11--->Insert----->Module
paste this code
----------------------------------
Option Explicit
Dim CellToCheck As Range
Dim CheckValue As String
Dim Cell As Range


Sub Checker()

For Each CellToCheck In Sheets("Sheet1").Range("B1:B20")
CheckValue = CellToCheck.Value & CellToCheck.Offset(0, 1).Value
For Each Cell In Sheets("Sheet2").Range("B1:B20")
If Cell.Value & Cell.Offset(0, 1).Value = CheckValue Then
Cell.EntireRow.Delete
End If
Next Cell
Next CellToCheck

End Sub
--------------------------------------

CLOSE THE VB EDITOR
after entering the data run Macro.
Agar aap chahte ho k jese he data enter karon tu sheet2 pa row delete ho jae tu reply.

[syar]

sheet 2 pa data delete hone se pehle aur baad m count karne k leye

sheet2 k tab pa right click karain. aur following code ko "Thisworkbook" k module m paste karain.

Jab bhee aap file open karain ge tu aap ko column a m data k count melele ga. Aur after delete count a k function k istimal se remaining data k count mele ga.

--------------------------
Private Sub Workbook_Open()
Sheets("sheet1").Range("A1").Value = Sheets("Sheet2").UsedRange.Rows.Count
Sheets("sheet1").Range("A1") = Sheets("sheet1").Range("A1").Value
Sheets("sheet1").Range("A2").Value = "=counta(Sheet2!B:B)"
End Sub
----------------

may be its will help you. If u have some other issue let me know.

regards

[engr.fahad]

Sorry for late reply.

to delete the row
Alt+F11--->Insert----->Module
paste this code
----------------------------------
Option Explicit
Dim CellToCheck As Range
Dim CheckValue As String
Dim Cell As Range


Sub Checker()

For Each CellToCheck In Sheets("Sheet1").Range("B1:B20")
CheckValue = CellToCheck.Value & CellToCheck.Offset(0, 1).Value
For Each Cell In Sheets("Sheet2").Range("B1:B20")
If Cell.Value & Cell.Offset(0, 1).Value = CheckValue Then
Cell.EntireRow.Delete
End If
Next Cell
Next CellToCheck

End Sub
--------------------------------------

CLOSE THE VB EDITOR
after entering the data run Macro.
Agar aap chahte ho k jese he data enter karon tu sheet2 pa row delete ho jae tu reply.

sheet 2 pa data delete hone se pehle aur baad m count karne k leye

sheet2 k tab pa right click karain. aur following code ko "Thisworkbook" k module m paste karain.

Jab bhee aap file open karain ge tu aap ko column a m data k count melele ga. Aur after delete count a k function k istimal se remaining data k count mele ga.

--------------------------
Private Sub Workbook_Open()
Sheets("sheet1").Range("A1").Value = Sheets("Sheet2").UsedRange.Rows.Count
Sheets("sheet1").Range("A1") = Sheets("sheet1").Range("A1").Value
Sheets("sheet1").Range("A2").Value = "=counta(Sheet2!B:B)"
End Sub
----------------

may be its will help you. If u have some other issue let me know.

regards

WSLM::



dear agr app mind na kerain to kindly sanpshots b upload ker dain plz

[syar]

salam::

aap ko post#7 m jo code deya tha us ko is code se change kar dain.

Option Explicit
Dim CellToCheck As Range
Dim CheckValue As String
Dim Cell As Range
Private Sub Worksheet_Change(ByVal Target As Range)

For Each CellToCheck In Sheets("Sheet1").Range("b1:b200")
CheckValue = CellToCheck.Value & CellToCheck.Offset(0, 1).Value
For Each Cell In Sheets("Sheet2").Range("b1:b200")
If Cell.Value & Cell.Offset(0, 1).Value = CheckValue Then
Cell.EntireRow.Delete
End If
Next Cell
Next CellToCheck
End Sub

m ne aap ki post dosre forum pa bhe deekhe ha.
Is code ko enter karne k leye aap sheet1 k tab par right clik karain aur view code select kar k ye code paste kar dain.
Ab aap jaise he column b m data enter karain ga agar wo sheet2 k column "B" m hoga tu delete ho gae ga.

post#8 m jo code lekha ha us ko waise he istimal karain.

agar screen shot ki zaroorat pare tu m mazrat chahta hon mujh ko dena nahee ata. App habibrahat se request karain wo xl2007 istimal karte hain wo aapko screen shot k sath samjha dainge.

[atharmirza]

Nice sharing

[Kaleem Nazeer]

As-salama-o-Alaikum!

SUBJECT: Excel Formula Chahiye

1. Main aik government mulazim hoon or humain aksar okat kisi bhi employee ki date of birth kay hisaab say date of superannuation yaani retiremen ki tareekh maloom karni parti hai. Jaisa ke aik mulazam ki date of birth 01-05-1970 hai or is nain 60 ssal ki umar main retire hona hai to hum kon sa formula istamal karain gay k humain maloom ho jaye ke falan date is ki retirement ki date ho gi. Example!
Sr. No. Name Date of Birth Date of Superannuation
1. Fawad 01-05-1970

Aksar okat humain aik jaga say dusri jaga transfer kiye gaye mulazmin ka taenati period maloom karna parta hai kay falan mulazim 04-05-1995 say 01-03-1998 tak aik jaga taenat raha to wo wahan kitnay saal, kitnay maheenay or kitnay dain tak kaam karta raha. Example!
Sr. No. Name From To Y M D
1. Central Jail LHR 04-06-1995 01-04-1998 00 00 00