Previous Topic Next Topic

Selecting the required cells info from the co

Solved/Closed
Shashi - Apr 28, 2010 at 01:18 AM
 Shashi - Apr 29, 2010 at 04:41 AM
Hi,
I have a data like below in one column in excel:

SL#00000
Product: ABC
Region
Name: Name1
City :1
Country:1
User con:
Id type
SL#888888
Product: xyz
Client:
Center ID
Name: Name2
City :2
Country:2
..............
...........N data
There is a big list in column imported from delimited text file. Now i want to select data on my choice like base on Sl#, Name, City, country in another work sheet which must show me the output as follows:

Sl# Name1, City1, Country1
Sl# Name2, City2, Country2
..................................N

Is there any way to select the cell based on the information/data not on the cell number as there are different information in between which i do not want expect the above mentioned.

Hope I am clear in my problem.

Thanks
Shashi

2 replies

Answer 1 / 2
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 808
Apr 28, 2010 at 07:20 AM
your sample data is in this url

https://authentification.site/files/22168388/sashi.xls

there are three macros in the vb editor . the sheet2 is after running the macro "test" all the three macros are to be parked in a module. But you should operate only "test" - the second macro is embedded in this.
the macro "undo" is to undo the results of the macro

you see sheet 2 in the uploaded file AFTER running the macro "test"
if you want to recheck
1.first run "undo"
2. second run macro "test"("test" only)

for completion sake I am giving the macros here itself also 

Sub test()
Worksheets("sheet1").Activate
Set cfind = Cells.Find(what:="SL#", after:=Cells(Rows.Count, "A"))
add = cfind.Address
j = cfind.Row
Do
'msgbox j
Set cfind = Cells.FindNext(cfind)
If cfind.Address = add Then
k = Cells(Rows.Count, "A").End(xlUp).Row
GoTo line1
End If
k = cfind.Row
line1:
'msgbox k
Range(Cells(j, "A"), Cells(k - 1, "B")).Copy
Worksheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial , Transpose:=True
If cfind.Address = add Then Exit Do
j = k
Loop
removeheadings
End Sub



Sub removeheadings()
Worksheets("sheet2").Activate
Range("A1").EntireRow.Delete
Do
Set cfind = Cells.Find(what:="SL#", after:=Range("A1"))
'msgbox cfind.Address
If cfind Is Nothing Then Exit Do
If cfind.Address = "$A$1" Then Exit Do
cfind.EntireRow.Delete
Loop
Application.CutCopyMode = False
Range("A1").Select
End Sub


Sub undo()
Worksheets("sheet2").Cells.Clear
End Sub
1
Answer 2 / 2
Shashi
Apr 29, 2010 at 04:41 AM
Hey..Thank you very much....this works brilliantly................................
0