Find and replace in EXCEL

Closed
Talahjer - Jan 26, 2011 at 02:37 PM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jan 28, 2011 at 08:01 PM
Hello, all

i have been suffering to find a solution for a problem which is :
I have three categories which I call region (Central, Northern and Southern) and actually there are eight governorates that comes under each region total of 24 districts. What I am looking for is when someone enters for example C1 or C2 or C3 or C4 or C5 or C6 or C7 or C8 in cell A1it automatically enters Central in cell B1 and if enters N1 or N2 or N3 or N4 or N5 or N6 or N7 or N8 it reflects Northern and the same for the Northern.
Please if anyone knows how to solve this I would be really grateful.
Thanks in advance.




6 replies

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Jan 27, 2011 at 06:21 AM
suppose sheet 1 is your sheet.
open vb editor (alt+F11)
click control+R
on the left "project" window opens
go to your file therein and righclick sheet1 and click view code
in the resulting window copy this event code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, mystring, j As Integer
If Target.Address <> "$A$1" Then Exit Sub
mystring = Array("N", "C", "S")
For j = 0 To 2
If Target = mystring(j) Then
Set r = Target.Offset(0, 1)
r = mystring(j) & 1
r.AutoFill Destination:=Range(r, r.Offset(7, 0)), Type:=xlFillDefault
End If
Next j
End Sub



now go to A1 type N or C or S see what happens in column B
0
Dear Venkat 1926

I really appreciate you effort; however, what you gave me was not quite what I was looking for. Anyway if will write my question in another way.
My regions and Districts are as follow:

Northern: Sadah, Amran, Al-Jawf, Marib, Hajjah and Al-Hudaydah
Central: Sanaa, Dhamar, Ibb, Raymah, Al-Bayda, Al-Mahwit and Taiz
Southern: Aden, Abyan, Dhale, Lahij, Shabwah, Hadramaut and Al-Mahrah

In the H:H i write the details of some incidents which come like this:
On 19 Jan 11, Sadah Governorate, Al-Hada District, etc.............
In C:C comes the Region, according to the above-mentioned incident, C1 should be Northern, because if you see that Sadah Governorate comes under the Northern region.

Let's imagine that I wrote this incident in H2:
On 19 Jan 11, Aden Governorate, Kreator District, etc.............
Then under C2, Northern should be written automatically.

i hope you understand me now.

thank you very much in advance.
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Jan 27, 2011 at 08:11 PM
it is ok if you write
"On 19 Jan 11, Aden Governorate, Kreator District, etc............. "
in H2.

the name of the governorate is Aden.
how can the computer find out the name of the governarate in the text.. Like a human being it cannot search the text and guess the name of the governoarate. If the name of the governorate comes always the second word after comma in the text it is possible to write a macro (by the by Aden is in southern region and not northern region.

suppose in another text it is written
"governaorate of Abyan ............"
the name of the goverenorate is not the second word after comma. and the computer will be confused.

It is therefore better if the user use his discretion to choose the name of hte governaorate and enter it in H2.

clarify these points. Remember computer is only a high speed idiot. better post a small extract of your data sheet with two or three texts.
0
dear Venkat1926

try this code.
insert the following code in C1 and drag down to C5
in D1 insert Sanaa and in D2 insert Taiz and in D3 insert Dhamar and in D4 insert Ibb
please see what happens in C1 to C4.

i tried to add more arguments but he Excel refused o add more.

regards
0
try this code.
insert the following code in C1 and drag down to C5

=IF(COUNTIF(D1,"*"&"Sanaa"&"*"),"Central",IF(COUNTIF(D1,"*"&"Dhamar"&"*"),"Central",IF(COUNTIF(D1,"*"&"Ibb"&"*"),"Central",IF(COUNTIF(D1,"*"&"Raymah"&"*"),"Central",IF(COUNTIF(D1,"*"&"Taiz"&"*"),"Central",IF(COUNTIF(D1,"*"&"Al_Bayda"&"*"),"Central",IF(COUNTIF(D1,"*"&"Al_Mahwit"&"*"),"Central")))))))


in D1 insert Sanaa and in D2 insert Taiz and in D3 insert Dhamar and in D4 insert Ibb
please see what happens in C1 to C4.

i tried to add more arguments but he Excel refused o add more.

regards
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Jan 28, 2011 at 05:18 AM
there is problem in nested ifs in excel 2003 and earlier. there is a limit of 7 nests.
0

Didn't find the answer you are looking for?

Ask a question
only 7. ok is there any other way to solve what i am looking for.
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Jan 28, 2011 at 08:01 PM
some other tactics have to be used like vlookup, choose etc. depending upon the data base.

on the other hand excel 2007 do not have these restrictions, I suppose
0