$$Excel-Macros$$ Re: Error Cleaning

[bpascal123]

Hi,

You say:

I need your help again.We extract the data from database & each user got 
> some errors on different projects.I need to clean the error data to get the 
> count of errors.For this,i do text to column on error column 


 What settings do you apply for text to columns?

If you first choose "Delimited" then tick "Comma" delimiters box you should 
get your error column automatically split into 3 columns which should then 
be easier to process manually or with vba if someone wants to code that.




Regards,

Pascal Baro
bpascal...@gmail.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com

$$Excel-Macros$$ Re: Error Cleaning

[SG]

Sorry for Replying late.Yes, i choose delimit & choose comma for it.Problem 
is this that the data is in thousandsr rows & manually it takes so much 
time.i have attached a file & i hope it clears the doubts brfore cleaning 
the data & after cleaning the data.

On Friday, July 13, 2012 5:33:35 PM UTC+5:30, SG wrote:

> Hi Experts,
>  
> I need your help again.We extract the data from database & each user got 
> some errors on different projects.I need to clean the error data to get the 
> count of errors.For this,i do text to column on error column.Then, in each 
> column after error column, i take the value one by one & copy paste the 
> data below the original data.It's too time consuming as data may expand up 
> to many columns & each column has irregular no. of errors description.I 
> have tried to explain best & need a macro to make unique row of erros of 
> all users.I have attached the excel file & a document in which i have tried 
> to explain.Looking forward for your help.
>  
> Thanks in advance.
>

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com

Book4.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

$$Excel-Macros$$ Re: Error Cleaning

[bpascal123]

Hi SG,

If you have so many rows and need to have data sorted in a table way, 
Access might be a better solution than Excel.
I understand you can have one and only Project number, one and only one 
person Name for a project and one to many error codes. I don't have strong 
Access skills but if you have thousand of rows for projects and any 
multiple for these for error codes Access should perform better than Excel.

Pascal Baro
bpascal...@gmail.com

On Monday, July 16, 2012 9:42:48 AM UTC+1, SG wrote:
>
> Sorry for Replying late.Yes, i choose delimit & choose comma for 
> it.Problem is this that the data is in thousandsr rows & manually it takes 
> so much time.i have attached a file & i hope it clears the doubts brfore 
> cleaning the data & after cleaning the data.
>
> On Friday, July 13, 2012 5:33:35 PM UTC+5:30, SG wrote:
>
>> Hi Experts,
>>  
>> I need your help again.We extract the data from database & each user got 
>> some errors on different projects.I need to clean the error data to get the 
>> count of errors.For this,i do text to column on error column.Then, in each 
>> column after error column, i take the value one by one & copy paste the 
>> data below the original data.It's too time consuming as data may expand up 
>> to many columns & each column has irregular no. of errors description.I 
>> have tried to explain best & need a macro to make unique row of erros of 
>> all users.I have attached the excel file & a document in which i have tried 
>> to explain.Looking forward for your help.
>>  
>> Thanks in advance.
>>
>

-- 
-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com

$$Excel-Macros$$ Re: Error Cleaning

[SG]

Hi Pascal,
 
Thanks for the suggestion but after cleaning the data, we have to perform 
other things in excel.Ialso don't have knowledge of Access,I'll try it.
On Tuesday, July 17, 2012 1:03:29 PM UTC+5:30, bpascal123 wrote:

> Hi SG, 
>
> If you have so many rows and need to have data sorted in a table way, 
> Access might be a better solution than Excel.
> I understand you can have one and only Project number, one and only one 
> person Name for a project and one to many error codes. I don't have strong 
> Access skills but if you have thousand of rows for projects and any 
> multiple for these for error codes Access should perform better than Excel.
>
> Pascal Baro
> bpascal...@gmail.com
>
> On Monday, July 16, 2012 9:42:48 AM UTC+1, SG wrote: 
>>
>> Sorry for Replying late.Yes, i choose delimit & choose comma for 
>> it.Problem is this that the data is in thousandsr rows & manually it takes 
>> so much time.i have attached a file & i hope it clears the doubts brfore 
>> cleaning the data & after cleaning the data.
>>
>> On Friday, July 13, 2012 5:33:35 PM UTC+5:30, SG wrote:
>>
>>> Hi Experts,
>>>  
>>> I need your help again.We extract the data from database & each user got 
>>> some errors on different projects.I need to clean the error data to get the 
>>> count of errors.For this,i do text to column on error column.Then, in each 
>>> column after error column, i take the value one by one & copy paste the 
>>> data below the original data.It's too time consuming as data may expand up 
>>> to many columns & each column has irregular no. of errors description.I 
>>> have tried to explain best & need a macro to make unique row of erros of 
>>> all users.I have attached the excel file & a document in which i have tried 
>>> to explain.Looking forward for your help.
>>>  
>>> Thanks in advance.
>>>
>>

-- 
-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com

$$Excel-Macros$$ Re: Error Cleaning

[bpascal123]

It's difficult to understand and help as you've posted different version of 
your data, at first there you start with a certain number of columns and 
then you end up with different column like : Study Name Coder(SC) 1.Coder 
/Qer name for scorecards 2.Type Of Errors Final for Scorecard
I also try to understand Don's code but honestly it makes me more confused. 
Try to post the exact thing that you want, that is the data as it comes and 
the data as you exactly want it

-- 
-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com

$$Excel-Macros$$ Re: Error Cleaning

[SG]

Hi Experts,
 
Please find attached sheet for cleaning. What i want is this after 
splitting , each error gets a unique row like i manually done in the third 
sheet.Problem is that the no. of columns can increase & "Code used by user" 
will always be in the end,so, macro will copy the complete row from "code 
used by user" to "Region" Column & then overwrite the splitted value in the 
"Code used by Coder" value & same would be deleted from splitted column. If 
u need more explanation, please refer document in the my first post for 
this problem.I'm looking forward to the help.
 
Thanks
 

On Tuesday, July 17, 2012 8:32:12 PM UTC+5:30, bpascal123 wrote:

> It's difficult to understand and help as you've posted different version 
> of your data, at first there you start with a certain number of columns and 
> then you end up with different column like :  Study Name Coder(SC) 1.Coder 
> /Qer name for scorecards 2.Type Of Errors Final for Scorecard
> I also try to understand Don's code but honestly it makes me more 
> confused. Try to post the exact thing that you want, that is the data as it 
> comes and the data as you exactly want it
>

-- 
-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com




Book1.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

$$Excel-Macros$$ Re: Error Cleaning

[bpascal123]

 Hi SG,

This code below should help you. You can run it from the vba ide in the 
attached file.
I'm not as comfortable as Don is with vba objects and methods so maybe 
Don's code is better for this.
I don't run if it takes time for many rows, ask again i like to search on 
the web trick to improve coding syntax.

Hope this answers what you need, 

Pascal Baro

Sub SGTest()

Dim wbIn As Workbook
Dim wsIn As Worksheet
Dim wsInOut As Worksheet
Dim v() As Variant
Dim r As Range
Dim rCol As Integer
Dim i As Long, j As Long
Dim lastr As Long, lastc As Long

Set wbIn = Workbooks("Book1 - Copy.xlsm")
'Set wsInOut = wbIn.Worksheets.Add: wsInOut.Name = "sorted data ok2"
Set wsInOut = wbIn.Worksheets("sorted data")
Set wsIn = wbIn.Worksheets("splitted data")

With Application
.Calculation = xlCalculationManual
.DisplayAlerts = False
.ScreenUpdating = False
End With

lastc = 0
wsIn.Rows(1).Copy wsInOut.Rows(1)
lastr = wsIn.Cells(Rows.Count, 8).End(xlUp).Row

For i = lastr To 2 Step -1

Set r = wsIn.Range(wsIn.Cells(i, 8), wsIn.Cells(i, 
wsIn.Columns.Count).End(xlToLeft))
rCol = r.Columns.Count

If rCol > 1 Then
v = Array(r)
wsIn.Rows(i + 1 & ":" & i + rCol - 1).Insert

wsIn.Range(wsIn.Cells(i, 1), wsIn.Cells(i, 7)).Copy _
wsIn.Range(wsIn.Cells(i + 1, 1), wsIn.Cells(i + rCol - 1, 7))

wsIn.Range(wsIn.Cells(i, 8), wsIn.Cells(i + rCol - 1, 8)).Value = _
Application.WorksheetFunction.Transpose(v)

If rCol > lastc Then lastc = rCol

End If

Next i

lastr = wsIn.Cells(Rows.Count, 8).End(xlUp).Row
wsIn.Range(wsIn.Cells(1, 9), wsIn.Cells(lastr, lastc + 9)).ClearContents

With Application
.Calculation = xlCalculationAutomatic
.DisplayAlerts = True
.ScreenUpdating = True
End With

Set r = Nothing
Set wbIn = Nothing
Set wsIn = Nothing
Set wsInOut = Nothing

End Sub

-- 
-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com




Book1 - Copy.xlsm
Description: application/vnd.ms-excel.sheet.macroenabled.12

$$Excel-Macros$$ Re: Error Cleaning

[SG]

hi pascal,
 
i didn't understand this part of code...what should i write if i run this 
macro in new workbook
 
 Set wbIn = Workbooks("Book1 - Copy.xlsm")
'Set wsInOut = wbIn.Worksheets.Add: wsInOut.Name = "sorted data ok2"
Set wsInOut = wbIn.Worksheets("sorted data")
Set wsIn = wbIn.Worksheets("splitted data")

On Friday, July 20, 2012 2:37:37 AM UTC+5:30, bpascal123 wrote:

>  Hi SG, 
>
> This code below should help you. You can run it from the vba ide in the 
> attached file.
> I'm not as comfortable as Don is with vba objects and methods so maybe 
> Don's code is better for this.
> I don't run if it takes time for many rows, ask again i like to search on 
> the web trick to improve coding syntax.
>
> Hope this answers what you need, 
>
> Pascal Baro
>
>  Sub SGTest()
>
> Dim wbIn As Workbook
> Dim wsIn As Worksheet
> Dim wsInOut As Worksheet
> Dim v() As Variant
> Dim r As Range
> Dim rCol As Integer
> Dim i As Long, j As Long
> Dim lastr As Long, lastc As Long
>
> Set wbIn = Workbooks("Book1 - Copy.xlsm")
> 'Set wsInOut = wbIn.Worksheets.Add: wsInOut.Name = "sorted data ok2"
> Set wsInOut = wbIn.Worksheets("sorted data")
> Set wsIn = wbIn.Worksheets("splitted data")
>
> With Application
> .Calculation = xlCalculationManual
> .DisplayAlerts = False
> .ScreenUpdating = False
> End With
>
> lastc = 0
> wsIn.Rows(1).Copy wsInOut.Rows(1)
> lastr = wsIn.Cells(Rows.Count, 8).End(xlUp).Row
>
> For i = lastr To 2 Step -1
>
> Set r = wsIn.Range(wsIn.Cells(i, 8), wsIn.Cells(i, 
> wsIn.Columns.Count).End(xlToLeft))
> rCol = r.Columns.Count
> 
> If rCol > 1 Then
> v = Array(r)
> wsIn.Rows(i + 1 & ":" & i + rCol - 1).Insert
> 
> wsIn.Range(wsIn.Cells(i, 1), wsIn.Cells(i, 7)).Copy _
> wsIn.Range(wsIn.Cells(i + 1, 1), wsIn.Cells(i + rCol - 1, 7))
> 
> wsIn.Range(wsIn.Cells(i, 8), wsIn.Cells(i + rCol - 1, 8)).Value = _
> Application.WorksheetFunction.Transpose(v)
> 
> If rCol > lastc Then lastc = rCol
> 
> End If
> 
> Next i
>
> lastr = wsIn.Cells(Rows.Count, 8).End(xlUp).Row
> wsIn.Range(wsIn.Cells(1, 9), wsIn.Cells(lastr, lastc + 9)).ClearContents
>
> With Application
> .Calculation = xlCalculationAutomatic
> .DisplayAlerts = True
> .ScreenUpdating = True
> End With
>
> Set r = Nothing
> Set wbIn = Nothing
> Set wsIn = Nothing
> Set wsInOut = Nothing
>
> End Sub
>
>

-- 
-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com

$$Excel-Macros$$ Re: Error Cleaning

[bpascal123]

This line should allow you to run this macro from the workbook where the 
data is ... if the file is not xlsx (it can then be xls, xlsm, xlsb )
Set wbIn = ThisWorkbook

(the line...worksheet add... that was commented out i forgot to delete 
wouldn't work anyway-i just found it easier to run the code directly on the 
splitted data sheet)

and one more change in the complete code below (with method was missing in 
what i sent previously):

---
Sub SGTest()

Dim wbIn As Workbook
Dim wsIn As Worksheet
Dim wsInOut As Worksheet
Dim v() As Variant
Dim r As Range
Dim rCol As Integer
Dim i As Long, j As Long
Dim lastr As Long, lastc As Long

Set wbIn = ThisWorkbook
Set wsInOut = wbIn.Worksheets("sorted data")
Set wsIn = wbIn.Worksheets("splitted data")

With Application
.Calculation = xlCalculationManual
.DisplayAlerts = False
.ScreenUpdating = False
End With

With wsIn

lastc = 0
.Rows(1).Copy wsInOut.Rows(1)
lastr = .Cells(Rows.Count, 8).End(xlUp).Row

For i = lastr To 2 Step -1

Set r = .Range(.Cells(i, 8), .Cells(i, 
.Columns.Count).End(xlToLeft))
rCol = r.Columns.Count

If rCol > 1 Then
v = Array(r)
.Rows(i + 1 & ":" & i + rCol - 1).Insert

.Range(.Cells(i, 1), .Cells(i, 7)).Copy .Range(.Cells(i + 1, 
1), .Cells(i + rCol - 1, 7))

.Range(.Cells(i, 8), .Cells(i + rCol - 1, 8)).Value = _
Application.WorksheetFunction.Transpose(v)

If rCol > lastc Then lastc = rCol

End If

Next i

lastr = .Cells(Rows.Count, 8).End(xlUp).Row
.Range(wsIn.Cells(1, 9), .Cells(lastr, lastc + 9)).ClearContents

End With


With Application
.Calculation = xlCalculationAutomatic
.DisplayAlerts = True
.ScreenUpdating = True
End With

Set r = Nothing
Set wbIn = Nothing
Set wsIn = Nothing
Set wsInOut = Nothing

End Sub

---
Pascal


On Friday, July 20, 2012 2:34:04 PM UTC+1, SG wrote:
>
> hi pascal,
>  
> i didn't understand this part of code...what should i write if i run this 
> macro in new workbook
>  
>  Set wbIn = Workbooks("Book1 - Copy.xlsm")
> 'Set wsInOut = wbIn.Worksheets.Add: wsInOut.Name = "sorted data ok2"
> Set wsInOut = wbIn.Worksheets("sorted data")
> Set wsIn = wbIn.Worksheets("splitted data")
>

-- 
-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com

$$Excel-Macros$$ Re: Error Cleaning

[SG]

hi Pascal
 
Thanku so muchit really helped meappreciate your help.& even it 
increased my knowledge in VB...
plz explain below part of code...& thanks again
 
If rCol > 1 Then 
v = Array(r)
.Rows(i + 1 & ":" & i + rCol - 1).Insert

.Range(.Cells(i, 1), .Cells(i, 7)).Copy .Range(.Cells(i + 1, 
1), .Cells(i + rCol - 1, 7))

.Range(.Cells(i, 8), .Cells(i + rCol - 1, 8)).Value = _

On Saturday, July 21, 2012 9:05:09 PM UTC+5:30, bpascal123 wrote:

>
> This line should allow you to run this macro from the workbook where the 
> data is ... if the file is not xlsx (it can then be xls, xlsm, xlsb ) 
> Set wbIn = ThisWorkbook
>
> (the line...worksheet add... that was commented out i forgot to delete 
> wouldn't work anyway-i just found it easier to run the code directly on the 
> splitted data sheet)
>
> and one more change in the complete code below (with method was missing in 
> what i sent previously):
>
> ---
>  Sub SGTest()
>
> Dim wbIn As Workbook
> Dim wsIn As Worksheet
> Dim wsInOut As Worksheet
> Dim v() As Variant
> Dim r As Range
> Dim rCol As Integer
> Dim i As Long, j As Long
> Dim lastr As Long, lastc As Long
>
> Set wbIn = ThisWorkbook
> Set wsInOut = wbIn.Worksheets("sorted data")
> Set wsIn = wbIn.Worksheets("splitted data")
>
> With Application
> .Calculation = xlCalculationManual
> .DisplayAlerts = False
> .ScreenUpdating = False
> End With
>
> With wsIn
>
> lastc = 0
> .Rows(1).Copy wsInOut.Rows(1)
> lastr = .Cells(Rows.Count, 8).End(xlUp).Row
>
> For i = lastr To 2 Step -1
> 
> Set r = .Range(.Cells(i, 8), .Cells(i, 
> .Columns.Count).End(xlToLeft))
> rCol = r.Columns.Count
> 
> If rCol > 1 Then
> v = Array(r)
> .Rows(i + 1 & ":" & i + rCol - 1).Insert
> 
> .Range(.Cells(i, 1), .Cells(i, 7)).Copy .Range(.Cells(i + 1, 
> 1), .Cells(i + rCol - 1, 7))
> 
> .Range(.Cells(i, 8), .Cells(i + rCol - 1, 8)).Value = _
> Application.WorksheetFunction.Transpose(v)
> 
> If rCol > lastc Then lastc = rCol
> 
> End If
> 
> Next i
> 
> lastr = .Cells(Rows.Count, 8).End(xlUp).Row
> .Range(wsIn.Cells(1, 9), .Cells(lastr, lastc + 9)).ClearContents
>
> End With
>
>
> With Application
> .Calculation = xlCalculationAutomatic
> .DisplayAlerts = True
> .ScreenUpdating = True
> End With
>
> Set r = Nothing
> Set wbIn = Nothing
> Set wsIn = Nothing
> Set wsInOut = Nothing
>
> End Sub
>
> ---
> Pascal
>
>
> On Friday, July 20, 2012 2:34:04 PM UTC+1, SG wrote: 
>>
>> hi pascal,
>>  
>> i didn't understand this part of code...what should i write if i run this 
>> macro in new workbook
>>  
>>  Set wbIn = Workbooks("Book1 - Copy.xlsm")
>> 'Set wsInOut = wbIn.Worksheets.Add: wsInOut.Name = "sorted data ok2"
>> Set wsInOut = wbIn.Worksheets("sorted data")
>> Set wsIn = wbIn.Worksheets("splitted data")
>>
>

-- 
-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com

$$Excel-Macros$$ Re: Error Cleaning

[bpascal123]

 My Vba coding style is much inspired from Kris who from what I know, posts 
here and on  excelfox.com. If you can read his code, you should learn many 
tricks.

If rCol > 1 Then
> v = Array(r)
> .Rows(i + 1 & ":" & i + rCol - 1).Insert
> 
> .Range(.Cells(i, 1), .Cells(i, 7)).Copy .Range(.Cells(i + 1, 
> 1), .Cells(i + rCol - 1, 7))
> 
> .Range(.Cells(i, 8), .Cells(i + rCol - 1, 8)).Value = _



v = Array(r) could actually be changed to v = r or v = r.value
v is an array that stores each code used by user column. Using an array was 
the only option since later the code calls a copy transpose method but as a 
range (opposite to array), copy transpose can't be used on the same range 
as for instance H8 would keep the first value, H9 the second. Using a range 
copy method, I would have had to insert an additional row to transpose the 
data and then delete the row 8, I hope you can follow me

.Rows(i + 1 & ":" & i + rCol - 1).Insert 
rCol is the variable that stores the number of code used by user, as i'm 
using an array, i can keep the value in the first row and add just a new 
row (still for H8 example)

 .Range(.Cells(i, 1), .Cells(i, 7)).Copy .Range(.Cells(i + 1, 1), .Cells(i 
+ rCol - 1, 7)) 
This is to copy the data from the initial line to the row i'm 
adding...rCol-1 same as line above rCol-1

.Range(.Cells(i, 8), .Cells(i + rCol - 1, 8)).Value = _
Application.WorksheetFunction.Transpose(v)

This took me some time to figure out, without this transpose function, I 
would have add to another loop... transpose just re-arrange data from row 
to column or the other way around, here v is the array that stores the code 
used by user values for one project or one row.

If rCol > lastc Then lastc = rCol 

This is for cleaning purpose, lastc stores the latest column used so to 
delete from H column to that column only instead of using a range H to 
columns.count clear contents method that could mess with your worksheet 
formatting or else

---
As these line are part of a loop, you can see this code in action by 
setting a break point (a big red dot on the left margin of a line of code) 
using the Vba IDE at the entry of the for loop, click Run until the break 
point you have inserted is highlighted in yellow, then minimize your Vba 
IDE window so you can see your worksheet and the data as well as some line 
of the Vba IDE (horizontal tile window like), then press once F8, look the 
position of the code advancing, and look for any changes on the data such 
as inserting rows, copy paste transpose...

You can also check the value of variables or object using the watch or 
debug window. The watch window is more straightforward since you just need 
to right click a variable like v or an object like r and select "add 
watch", you will then be able to monitor values stored in an object or 
variable.


I think if you already have some knowledge of Vba, you can use these Vba 
tools as I'm trying to present you.

Pascal Baro


On Monday, July 23, 2012 2:03:26 PM UTC+1, SG wrote:
>
> hi Pascal
>  
> Thanku so muchit really helped meappreciate your help.& even 
> it increased my knowledge in VB...
> plz explain below part of code...& thanks again
>  
> If rCol > 1 Then 
> v = Array(r)
> .Rows(i + 1 & ":" & i + rCol - 1).Insert
> 
> .Range(.Cells(i, 1), .Cells(i, 7)).Copy .Range(.Cells(i + 1, 
> 1), .Cells(i + rCol - 1, 7))
> 
> .Range(.Cells(i, 8), .Cells(i + rCol - 1, 8)).Value = _
>

-- 
-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com

$$Excel-Macros$$ Re: Error Cleaning

[bpascal123]

This forum is a great place to learn, contributors I closely follow; 
Noorain, Asa, Don...

On Monday, July 23, 2012 5:25:31 PM UTC+1, bpascal123 wrote:
>
>  My Vba coding style is much inspired from Kris who from what I know, 
> posts here and on  excelfox.com. If you can read his code, you should 
> learn many tricks.
>
> If rCol > 1 Then
>> v = Array(r)
>> .Rows(i + 1 & ":" & i + rCol - 1).Insert
>> 
>> .Range(.Cells(i, 1), .Cells(i, 7)).Copy .Range(.Cells(i + 1, 
>> 1), .Cells(i + rCol - 1, 7))
>> 
>> .Range(.Cells(i, 8), .Cells(i + rCol - 1, 8)).Value = _
>
>
>
> v = Array(r) could actually be changed to v = r or v = r.value
> v is an array that stores each code used by user column. Using an array 
> was the only option since later the code calls a copy transpose method but 
> as a range (opposite to array), copy transpose can't be used on the same 
> range as for instance H8 would keep the first value, H9 the second. Using a 
> range copy method, I would have had to insert an additional row to 
> transpose the data and then delete the row 8, I hope you can follow me
>
> .Rows(i + 1 & ":" & i + rCol - 1).Insert 
> rCol is the variable that stores the number of code used by user, as i'm 
> using an array, i can keep the value in the first row and add just a new 
> row (still for H8 example)
>
>  .Range(.Cells(i, 1), .Cells(i, 7)).Copy .Range(.Cells(i + 1, 1), .Cells(i 
> + rCol - 1, 7)) 
> This is to copy the data from the initial line to the row i'm 
> adding...rCol-1 same as line above rCol-1
>
> .Range(.Cells(i, 8), .Cells(i + rCol - 1, 8)).Value = _
> Application.WorksheetFunction.Transpose(v)
>
> This took me some time to figure out, without this transpose function, I 
> would have add to another loop... transpose just re-arrange data from row 
> to column or the other way around, here v is the array that stores the code 
> used by user values for one project or one row.
>
> If rCol > lastc Then lastc = rCol 
>
> This is for cleaning purpose, lastc stores the latest column used so to 
> delete from H column to that column only instead of using a range H to 
> columns.count clear contents method that could mess with your worksheet 
> formatting or else
>
> ---
> As these line are part of a loop, you can see this code in action by 
> setting a break point (a big red dot on the left margin of a line of code) 
> using the Vba IDE at the entry of the for loop, click Run until the break 
> point you have inserted is highlighted in yellow, then minimize your Vba 
> IDE window so you can see your worksheet and the data as well as some line 
> of the Vba IDE (horizontal tile window like), then press once F8, look the 
> position of the code advancing, and look for any changes on the data such 
> as inserting rows, copy paste transpose...
>
> You can also check the value of variables or object using the watch or 
> debug window. The watch window is more straightforward since you just need 
> to right click a variable like v or an object like r and select "add 
> watch", you will then be able to monitor values stored in an object or 
> variable.
>
>
> I think if you already have some knowledge of Vba, you can use these Vba 
> tools as I'm trying to present you.
>
> Pascal Baro
>
>
> On Monday, July 23, 2012 2:03:26 PM UTC+1, SG wrote:
>>
>> hi Pascal
>>  
>> Thanku so muchit really helped meappreciate your help.& even 
>> it increased my knowledge in VB...
>> plz explain below part of code...& thanks again
>>  
>> If rCol > 1 Then 
>> v = Array(r)
>> .Rows(i + 1 & ":" & i + rCol - 1).Insert
>> 
>> .Range(.Cells(i, 1), .Cells(i, 7)).Copy .Range(.Cells(i + 1, 
>> 1), .Cells(i + rCol - 1, 7))
>> 
>> .Range(.Cells(i, 8), .Cells(i + rCol - 1, 8)).Value = _
>>
>

-- 
-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com

$$Excel-Macros$$ Re: Error Cleaning

[SG]

hi pascal,
 
Thanks for explaining.It really helped me.I'm a beginner in macros so i 
used to follow all experts.

On Tuesday, July 24, 2012 5:48:08 PM UTC+5:30, bpascal123 wrote:

> This forum is a great place to learn, contributors I closely follow; 
> Noorain, Asa, Don...
>
> On Monday, July 23, 2012 5:25:31 PM UTC+1, bpascal123 wrote: 
>>
>>  My Vba coding style is much inspired from Kris who from what I know, 
>> posts here and on  excelfox.com. If you can read his code, you should 
>> learn many tricks. 
>>
>> If rCol > 1 Then
>>> v = Array(r)
>>> .Rows(i + 1 & ":" & i + rCol - 1).Insert
>>> 
>>> .Range(.Cells(i, 1), .Cells(i, 7)).Copy .Range(.Cells(i + 1, 
>>> 1), .Cells(i + rCol - 1, 7))
>>> 
>>> .Range(.Cells(i, 8), .Cells(i + rCol - 1, 8)).Value = _
>>
>>  
>>
>> v = Array(r) could actually be changed to v = r or v = r.value
>> v is an array that stores each code used by user column. Using an array 
>> was the only option since later the code calls a copy transpose method but 
>> as a range (opposite to array), copy transpose can't be used on the same 
>> range as for instance H8 would keep the first value, H9 the second. Using a 
>> range copy method, I would have had to insert an additional row to 
>> transpose the data and then delete the row 8, I hope you can follow me
>>
>> .Rows(i + 1 & ":" & i + rCol - 1).Insert 
>> rCol is the variable that stores the number of code used by user, as i'm 
>> using an array, i can keep the value in the first row and add just a new 
>> row (still for H8 example)
>>
>>  .Range(.Cells(i, 1), .Cells(i, 7)).Copy .Range(.Cells(i + 1, 1), 
>> .Cells(i + rCol - 1, 7)) 
>> This is to copy the data from the initial line to the row i'm 
>> adding...rCol-1 same as line above rCol-1
>>
>>  .Range(.Cells(i, 8), .Cells(i + rCol - 1, 8)).Value = _
>> Application.WorksheetFunction.Transpose(v)
>>
>> This took me some time to figure out, without this transpose function, I 
>> would have add to another loop... transpose just re-arrange data from row 
>> to column or the other way around, here v is the array that stores the code 
>> used by user values for one project or one row.
>>
>> If rCol > lastc Then lastc = rCol 
>>
>> This is for cleaning purpose, lastc stores the latest column used so to 
>> delete from H column to that column only instead of using a range H to 
>> columns.count clear contents method that could mess with your worksheet 
>> formatting or else
>>
>> ---
>> As these line are part of a loop, you can see this code in action by 
>> setting a break point (a big red dot on the left margin of a line of code) 
>> using the Vba IDE at the entry of the for loop, click Run until the break 
>> point you have inserted is highlighted in yellow, then minimize your Vba 
>> IDE window so you can see your worksheet and the data as well as some line 
>> of the Vba IDE (horizontal tile window like), then press once F8, look the 
>> position of the code advancing, and look for any changes on the data such 
>> as inserting rows, copy paste transpose...
>>
>> You can also check the value of variables or object using the watch or 
>> debug window. The watch window is more straightforward since you just need 
>> to right click a variable like v or an object like r and select "add 
>> watch", you will then be able to monitor values stored in an object or 
>> variable.
>>
>>
>> I think if you already have some knowledge of Vba, you can use these Vba 
>> tools as I'm trying to present you.
>>
>> Pascal Baro
>>
>>
>> On Monday, July 23, 2012 2:03:26 PM UTC+1, SG wrote: 
>>>
>>> hi Pascal
>>>  
>>> Thanku so muchit really helped meappreciate your help.& even 
>>> it increased my knowledge in VB...
>>> plz explain below part of code...& thanks again
>>>  
>>> If rCol > 1 Then 
>>> v = Array(r)
>>> .Rows(i + 1 & ":" & i + rCol - 1).Insert
>>> 
>>> .Range(.Cells(i, 1), .Cells(i, 7)).Copy .Range(.Cells(i + 1, 
>>> 1), .Cells(i + rCol - 1, 7))
>>> 
>>> .Range(.Cells(i, 8), .Cells(i + rCol - 1, 8)).Value = _
>>>
>>

-- 
-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, s

Re: $$Excel-Macros$$ Re: Error Cleaning

[╰» ℓαℓιт мσαнη]

Hi,

Please attach file with sample data to make it more clear.

Regards,
Lalit Mohan


On Sat, Jul 14, 2012 at 3:11 AM, bpascal123  wrote:

> Hi,
>
> You say:
>
> I need your help again.We extract the data from database & each user got
>> some errors on different projects.I need to clean the error data to get the
>> count of errors.For this,i do text to column on error column
>
>
>  What settings do you apply for text to columns?
>
> If you first choose "Delimited" then tick "Comma" delimiters box you
> should get your error column automatically split into 3 columns which
> should then be easier to process manually or with vba if someone wants to
> code that.
>
>
> 
>
>
> Regards,
>
> Pascal Baro
> bpascal...@gmail.com
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
> To unsubscribe, send a blank email to
> excel-macros+unsubscr...@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com

Re: $$Excel-Macros$$ Re: Error Cleaning

[dguillett1]

Should do it

Option Explicit
Sub getlists()
Dim lr As Long
Dim i As Long
Dim dr As Long
Dim btc As Range
lr = Cells(Rows.Count, 1).End(xlUp).Row - 1
Set btc = Cells(2, 1).Resize(lr, 2)
For i = 2 To lr + 1
dr = Cells(Rows.Count, 1).End(xlUp).Row + 1
btc.Copy Cells(dr, 1)
Cells(2, i + 1).Resize(lr).Copy Cells(dr, "c")
'MsgBox i
Next i
Columns(3).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
‘’
Don Guillett
Microsoft Excel Developer
SalesAid Software
dguille...@gmail.com

From: SG 
Sent: Monday, July 16, 2012 3:42 AM
To: excel-macros@googlegroups.com 
Subject: $$Excel-Macros$$ Re: Error Cleaning

Sorry for Replying late.Yes, i choose delimit & choose comma for it.Problem is 
this that the data is in thousandsr rows & manually it takes so much time.i 
have attached a file & i hope it clears the doubts brfore cleaning the data & 
after cleaning the data.

On Friday, July 13, 2012 5:33:35 PM UTC+5:30, SG wrote:
  Hi Experts,

  I need your help again.We extract the data from database & each user got some 
errors on different projects.I need to clean the error data to get the count of 
errors.For this,i do text to column on error column.Then, in each column after 
error column, i take the value one by one & copy paste the data below the 
original data.It's too time consuming as data may expand up to many columns & 
each column has irregular no. of errors description.I have tried to explain 
best & need a macro to make unique row of erros of all users.I have attached 
the excel file & a document in which i have tried to explain.Looking forward 
for your help.

  Thanks in advance.
-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
 
--
To post to this group, send email to excel-macros@googlegroups.com
 
To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com

Re: $$Excel-Macros$$ Re: Error Cleaning

[SG]

Hi Don,
 
Thanks for the help. I haven't run the macro.I'm a begineer to the 
macro.Can you please explain the "option Explicit" & use of "resize" & 
would i run this macro after text to column splitting?

uillett wrote:

>   Should do it
>  
> Option Explicit
> Sub getlists()
> Dim lr As Long
> Dim i As Long
> Dim dr As Long
> Dim btc As Range
> lr = Cells(Rows.Count, 1).End(xlUp).Row - 1
> Set btc = Cells(2, 1).Resize(lr, 2)
> For i = 2 To lr + 1
> dr = Cells(Rows.Count, 1).End(xlUp).Row + 1
> btc.Copy Cells(dr, 1)
> Cells(2, i + 1).Resize(lr).Copy Cells(dr, "c")
> 'MsgBox i
> Next i
> Columns(3).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
> End Sub
> ‘’
> Don Guillett
> Microsoft Excel Developer
> SalesAid Software
> dguille...@gmail.com
>   
>  *From:* SG  
> *Sent:* Monday, July 16, 2012 3:42 AM
> *To:* excel-macros@googlegroups.com 
> *Subject:* $$Excel-Macros$$ Re: Error Cleaning
>  
>  Sorry for Replying late.Yes, i choose delimit & choose comma for 
> it.Problem is this that the data is in thousandsr rows & manually it takes 
> so much time.i have attached a file & i hope it clears the doubts brfore 
> cleaning the data & after cleaning the data.
>
> On Friday, July 13, 2012 5:33:35 PM UTC+5:30, SG wrote:
>
>> Hi Experts,
>>  
>> I need your help again.We extract the data from database & each user got 
>> some errors on different projects.I need to clean the error data to get the 
>> count of errors.For this,i do text to column on error column.Then, in each 
>> column after error column, i take the value one by one & copy paste the 
>> data below the original data.It's too time consuming as data may expand up 
>> to many columns & each column has irregular no. of errors description.I 
>> have tried to explain best & need a macro to make unique row of erros of 
>> all users.I have attached the excel file & a document in which i have tried 
>> to explain.Looking forward for your help.
>>  
>> Thanks in advance.
>>
> -- 
> FORUM RULES (986+ members already BANNED for violation)
>  
> 1) Use concise, accurate thread titles. Poor thread titles, like Please 
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice 
> will not get quick attention or may not be answered.
>  
> 2) Don't post a question in the thread of another member.
>  
> 3) Don't post questions regarding breaking or bypassing any security 
> measure.
>  
> 4) Acknowledge the responses you receive, good or bad.
>  
> 5) Cross-promotion of, or links to, forums competitive to this forum in 
> signatures are prohibited. 
>  
> NOTE : Don't ever post personal or confidential data in a workbook. Forum 
> owners and members are not responsible for any loss.
>  
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>  
> To unsubscribe, send a blank email to 
> excel-macros+unsubscr...@googlegroups.com
>

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com

Re: $$Excel-Macros$$ Re: Error Cleaning

[dguillett1]

I wrote it to continue what you had already done. The TTC could be 
incorporated. 
Option explicit forces use of dim
Look in vba help index for resize

Don Guillett
Microsoft Excel Developer
SalesAid Software
dguille...@gmail.com

From: SG 
Sent: Monday, July 16, 2012 8:49 AM
To: excel-macros@googlegroups.com 
Subject: Re: $$Excel-Macros$$ Re: Error Cleaning

Hi Don,

Thanks for the help. I haven't run the macro.I'm a begineer to the macro.Can 
you please explain the "option Explicit" & use of "resize" & would i run this 
macro after text to column splitting?

uillett wrote:
  Should do it

  Option Explicit
  Sub getlists()
  Dim lr As Long
  Dim i As Long
  Dim dr As Long
  Dim btc As Range
  lr = Cells(Rows.Count, 1).End(xlUp).Row - 1
  Set btc = Cells(2, 1).Resize(lr, 2)
  For i = 2 To lr + 1
  dr = Cells(Rows.Count, 1).End(xlUp).Row + 1
  btc.Copy Cells(dr, 1)
  Cells(2, i + 1).Resize(lr).Copy Cells(dr, "c")
  'MsgBox i
  Next i
  Columns(3).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  End Sub
  ‘’
  Don Guillett
  Microsoft Excel Developer
  SalesAid Software
  dguille...@gmail.com

  From: SG 
  Sent: Monday, July 16, 2012 3:42 AM
  To: excel-macros@googlegroups.com 
  Subject: $$Excel-Macros$$ Re: Error Cleaning

  Sorry for Replying late.Yes, i choose delimit & choose comma for it.Problem 
is this that the data is in thousandsr rows & manually it takes so much time.i 
have attached a file & i hope it clears the doubts brfore cleaning the data & 
after cleaning the data.

  On Friday, July 13, 2012 5:33:35 PM UTC+5:30, SG wrote:
Hi Experts,

I need your help again.We extract the data from database & each user got 
some errors on different projects.I need to clean the error data to get the 
count of errors.For this,i do text to column on error column.Then, in each 
column after error column, i take the value one by one & copy paste the data 
below the original data.It's too time consuming as data may expand up to many 
columns & each column has irregular no. of errors description.I have tried to 
explain best & need a macro to make unique row of erros of all users.I have 
attached the excel file & a document in which i have tried to explain.Looking 
forward for your help.

Thanks in advance.
  -- 
  FORUM RULES (986+ members already BANNED for violation)
   
  1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
   
  2) Don't post a question in the thread of another member.
   
  3) Don't post questions regarding breaking or bypassing any security measure.
   
  4) Acknowledge the responses you receive, good or bad.
   
  5) Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 
   
  NOTE : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
   
  
--
  To post to this group, send email to excel-macros@googlegroups.com
   
  To unsubscribe, send a blank email to 
excel-macros+unsubscr...@googlegroups.com
-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
 
--
To post to this group, send email to excel-macros@googlegroups.com
 
To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are no

Re: $$Excel-Macros$$ Re: Error Cleaning

[SG]

Hi Don,
 
It's working perfectly.but one more problem is this i have shown only 3 
columns in my sample file but in real ...i have atleast 23 columns & last 
column is the Error column on which this macro will work...now this macro 
is consolidating the data at the end.Please have a look.For instance, i 
have increased the column no to 5.

On Monday, July 16, 2012 7:32:46 PM UTC+5:30, Don Guillett wrote:

>   I wrote it to continue what you had already done. The TTC could be 
> incorporated. 
> Option explicit forces use of dim
> Look in vba help index for resize
>  
> Don Guillett
> Microsoft Excel Developer
> SalesAid Software
> dguille...@gmail.com
>   
>  *From:* SG  
> *Sent:* Monday, July 16, 2012 8:49 AM
> *To:* excel-macros@googlegroups.com 
> *Subject:* Re: $$Excel-Macros$$ Re: Error Cleaning
>  
>  Hi Don,
>  
> Thanks for the help. I haven't run the macro.I'm a begineer to the 
> macro.Can you please explain the "option Explicit" & use of "resize" & 
> would i run this macro after text to column splitting?
>
> uillett wrote:
>
>>   Should do it
>>  
>> Option Explicit
>> Sub getlists()
>> Dim lr As Long
>> Dim i As Long
>> Dim dr As Long
>> Dim btc As Range
>> lr = Cells(Rows.Count, 1).End(xlUp).Row - 1
>> Set btc = Cells(2, 1).Resize(lr, 2)
>> For i = 2 To lr + 1
>> dr = Cells(Rows.Count, 1).End(xlUp).Row + 1
>> btc.Copy Cells(dr, 1)
>> Cells(2, i + 1).Resize(lr).Copy Cells(dr, "c")
>> 'MsgBox i
>> Next i
>> Columns(3).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
>> End Sub
>> ‘’’’’’
>> Don Guillett
>> Microsoft Excel Developer
>> SalesAid Software
>> dguille...@gmail.com
>>   
>>  *From:* SG  
>> *Sent:* Monday, July 16, 2012 3:42 AM
>> *To:* excel-macros@googlegroups.com 
>> *Subject:* $$Excel-Macros$$ Re: Error Cleaning
>>  
>>  Sorry for Replying late.Yes, i choose delimit & choose comma for 
>> it.Problem is this that the data is in thousandsr rows & manually it takes 
>> so much time.i have attached a file & i hope it clears the doubts brfore 
>> cleaning the data & after cleaning the data.
>>
>> On Friday, July 13, 2012 5:33:35 PM UTC+5:30, SG wrote:
>>
>>> Hi Experts,
>>>  
>>> I need your help again.We extract the data from database & each user got 
>>> some errors on different projects.I need to clean the error data to get the 
>>> count of errors.For this,i do text to column on error column.Then, in each 
>>> column after error column, i take the value one by one & copy paste the 
>>> data below the original data.It's too time consuming as data may expand up 
>>> to many columns & each column has irregular no. of errors description.I 
>>> have tried to explain best & need a macro to make unique row of erros of 
>>> all users.I have attached the excel file & a document in which i have tried 
>>> to explain.Looking forward for your help.
>>>  
>>> Thanks in advance.
>>>
>> -- 
>> FORUM RULES (986+ members already BANNED for violation)
>>  
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please 
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice 
>> will not get quick attention or may not be answered.
>>  
>> 2) Don't post a question in the thread of another member.
>>  
>> 3) Don't post questions regarding breaking or bypassing any security 
>> measure.
>>  
>> 4) Acknowledge the responses you receive, good or bad.
>>  
>> 5) Cross-promotion of, or links to, forums competitive to this forum in 
>> signatures are prohibited. 
>>  
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum 
>> owners and members are not responsible for any loss.
>>  
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>  
>> To unsubscribe, send a blank email to 
>> excel-macros+unsubscr...@googlegroups.com
>>
> -- 
> FORUM RULES (986+ members already BANNED for violation)
>  
> 1) Use concise, accurate thread titles. Poor thread titles, like Please 
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice 
> will not get quick attention or may not be answered.
>  
> 2) Don't post a question in the thread of another member.
>  
> 3) Don't post questions regarding breaki

Re: $$Excel-Macros$$ Re: Error Cleaning

[dguillett1]

I don’t understand. Look at before/after sheets to see what macro is doing.

Don Guillett
Microsoft Excel Developer
SalesAid Software
dguille...@gmail.com

From: SG 
Sent: Monday, July 16, 2012 9:30 AM
To: excel-macros@googlegroups.com 
Subject: Re: $$Excel-Macros$$ Re: Error Cleaning

Hi Don,

It's working perfectly.but one more problem is this i have shown only 3 columns 
in my sample file but in real ...i have atleast 23 columns & last column is the 
Error column on which this macro will work...now this macro is consolidating 
the data at the end.Please have a look.For instance, i have increased the 
column no to 5.

On Monday, July 16, 2012 7:32:46 PM UTC+5:30, Don Guillett wrote:
  I wrote it to continue what you had already done. The TTC could be 
incorporated. 
  Option explicit forces use of dim
  Look in vba help index for resize

  Don Guillett
  Microsoft Excel Developer
  SalesAid Software
  dguille...@gmail.com

  From: SG 
  Sent: Monday, July 16, 2012 8:49 AM
  To: excel-macros@googlegroups.com 
  Subject: Re: $$Excel-Macros$$ Re: Error Cleaning

  Hi Don,

  Thanks for the help. I haven't run the macro.I'm a begineer to the macro.Can 
you please explain the "option Explicit" & use of "resize" & would i run this 
macro after text to column splitting?

  uillett wrote:
Should do it

Option Explicit
Sub getlists()
Dim lr As Long
Dim i As Long
Dim dr As Long
Dim btc As Range
lr = Cells(Rows.Count, 1).End(xlUp).Row - 1
Set btc = Cells(2, 1).Resize(lr, 2)
For i = 2 To lr + 1
dr = Cells(Rows.Count, 1).End(xlUp).Row + 1
btc.Copy Cells(dr, 1)
Cells(2, i + 1).Resize(lr).Copy Cells(dr, "c")
'MsgBox i
Next i
Columns(3).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
‘’
Don Guillett
Microsoft Excel Developer
SalesAid Software
dguille...@gmail.com

From: SG 
Sent: Monday, July 16, 2012 3:42 AM
To: excel-macros@googlegroups.com 
Subject: $$Excel-Macros$$ Re: Error Cleaning

Sorry for Replying late.Yes, i choose delimit & choose comma for it.Problem 
is this that the data is in thousandsr rows & manually it takes so much time.i 
have attached a file & i hope it clears the doubts brfore cleaning the data & 
after cleaning the data.

On Friday, July 13, 2012 5:33:35 PM UTC+5:30, SG wrote:
  Hi Experts,

  I need your help again.We extract the data from database & each user got 
some errors on different projects.I need to clean the error data to get the 
count of errors.For this,i do text to column on error column.Then, in each 
column after error column, i take the value one by one & copy paste the data 
below the original data.It's too time consuming as data may expand up to many 
columns & each column has irregular no. of errors description.I have tried to 
explain best & need a macro to make unique row of erros of all users.I have 
attached the excel file & a document in which i have tried to explain.Looking 
forward for your help.

  Thanks in advance.
-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please 
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will 
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security 
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com
 
To unsubscribe, send a blank email to 
excel-macros+unsubscr...@googlegroups.com
  -- 
  FORUM RULES (986+ members already BANNED for violation)
   
  1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
   
  2) Don't post a question in the thread of another member.
   
  3) Don't post questions regarding breaking or bypassing any security measure.
   
  4) Acknowledge the responses you receive, good or bad.
   
  5) Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 
   
  NOTE : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
   
  
---

Re: $$Excel-Macros$$ Re: Error Cleaning

[dguillett1]

Don Guillett
Microsoft Excel Developer
SalesAid Software
dguille...@gmail.com

From: SG 
Sent: Monday, July 16, 2012 9:30 AM
To: excel-macros@googlegroups.com 
Subject: Re: $$Excel-Macros$$ Re: Error Cleaning

Hi Don,

It's working perfectly.but one more problem is this i have shown only 3 columns 
in my sample file but in real ...i have atleast 23 columns & last column is the 
Error column on which this macro will work...now this macro is consolidating 
the data at the end.Please have a look.For instance, i have increased the 
column no to 5.

On Monday, July 16, 2012 7:32:46 PM UTC+5:30, Don Guillett wrote:
  I wrote it to continue what you had already done. The TTC could be 
incorporated. 
  Option explicit forces use of dim
  Look in vba help index for resize

  Don Guillett
  Microsoft Excel Developer
  SalesAid Software
  dguille...@gmail.com

  From: SG 
  Sent: Monday, July 16, 2012 8:49 AM
  To: excel-macros@googlegroups.com 
  Subject: Re: $$Excel-Macros$$ Re: Error Cleaning

  Hi Don,

  Thanks for the help. I haven't run the macro.I'm a begineer to the macro.Can 
you please explain the "option Explicit" & use of "resize" & would i run this 
macro after text to column splitting?

  uillett wrote:
Should do it

Option Explicit
Sub getlists()
Dim lr As Long
Dim i As Long
Dim dr As Long
Dim btc As Range
lr = Cells(Rows.Count, 1).End(xlUp).Row - 1
Set btc = Cells(2, 1).Resize(lr, 2)
For i = 2 To lr + 1
dr = Cells(Rows.Count, 1).End(xlUp).Row + 1
btc.Copy Cells(dr, 1)
Cells(2, i + 1).Resize(lr).Copy Cells(dr, "c")
'MsgBox i
Next i
Columns(3).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
‘’
Don Guillett
Microsoft Excel Developer
SalesAid Software
dguille...@gmail.com

From: SG 
Sent: Monday, July 16, 2012 3:42 AM
To: excel-macros@googlegroups.com 
Subject: $$Excel-Macros$$ Re: Error Cleaning

Sorry for Replying late.Yes, i choose delimit & choose comma for it.Problem 
is this that the data is in thousandsr rows & manually it takes so much time.i 
have attached a file & i hope it clears the doubts brfore cleaning the data & 
after cleaning the data.

On Friday, July 13, 2012 5:33:35 PM UTC+5:30, SG wrote:
  Hi Experts,

  I need your help again.We extract the data from database & each user got 
some errors on different projects.I need to clean the error data to get the 
count of errors.For this,i do text to column on error column.Then, in each 
column after error column, i take the value one by one & copy paste the data 
below the original data.It's too time consuming as data may expand up to many 
columns & each column has irregular no. of errors description.I have tried to 
explain best & need a macro to make unique row of erros of all users.I have 
attached the excel file & a document in which i have tried to explain.Looking 
forward for your help.

  Thanks in advance.
-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please 
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will 
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security 
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com
 
To unsubscribe, send a blank email to 
excel-macros+unsubscr...@googlegroups.com
  -- 
  FORUM RULES (986+ members already BANNED for violation)
   
  1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
   
  2) Don't post a question in the thread of another member.
   
  3) Don't post questions regarding breaking or bypassing any security measure.
   
  4) Acknowledge the responses you receive, good or bad.
   
  5) Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 
   
  NOTE : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
   
  
--
  To post to this group, send em

Re: $$Excel-Macros$$ Re: Error Cleaning

[SG]

Yes, if you see..from row 12 to row 40, the data is blank..can it be 
possible after running the macro...it would consolidated without such 
blanks in the data??
On Monday, July 16, 2012 8:31:55 PM UTC+5:30, Don Guillett wrote: 
>
>
>  
> Don Guillett
> Microsoft Excel Developer
> SalesAid Software
> dguille...@gmail.com
>   
>  *From:* SG  
> *Sent:* Monday, July 16, 2012 9:30 AM
> *To:* excel-macros@googlegroups.com 
> *Subject:* Re: $$Excel-Macros$$ Re: Error Cleaning
>  
>  Hi Don,
>  
> It's working perfectly.but one more problem is this i have shown only 3 
> columns in my sample file but in real ...i have atleast 23 columns & last 
> column is the Error column on which this macro will work...now this macro 
> is consolidating the data at the end.Please have a look.For instance, i 
> have increased the column no to 5.
>
> On Monday, July 16, 2012 7:32:46 PM UTC+5:30, Don Guillett wrote:
>
>>   I wrote it to continue what you had already done. The TTC could be 
>> incorporated. 
>> Option explicit forces use of dim
>> Look in vba help index for resize
>>  
>> Don Guillett
>> Microsoft Excel Developer
>> SalesAid Software
>> dguille...@gmail.com
>>   
>>  *From:* SG  
>> *Sent:* Monday, July 16, 2012 8:49 AM
>> *To:* excel-macros@googlegroups.com 
>> *Subject:* Re: $$Excel-Macros$$ Re: Error Cleaning
>>  
>>  Hi Don,
>>  
>> Thanks for the help. I haven't run the macro.I'm a begineer to the 
>> macro.Can you please explain the "option Explicit" & use of "resize" & 
>> would i run this macro after text to column splitting?
>>
>> uillett wrote:
>>
>>>   Should do it
>>>  
>>> Option Explicit
>>> Sub getlists()
>>> Dim lr As Long
>>> Dim i As Long
>>> Dim dr As Long
>>> Dim btc As Range
>>> lr = Cells(Rows.Count, 1).End(xlUp).Row - 1
>>> Set btc = Cells(2, 1).Resize(lr, 2)
>>> For i = 2 To lr + 1
>>> dr = Cells(Rows.Count, 1).End(xlUp).Row + 1
>>> btc.Copy Cells(dr, 1)
>>> Cells(2, i + 1).Resize(lr).Copy Cells(dr, "c")
>>> 'MsgBox i
>>> Next i
>>> Columns(3).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
>>> End Sub
>>> ‘’
>>> Don Guillett
>>> Microsoft Excel Developer
>>> SalesAid Software
>>> dguille...@gmail.com
>>>   
>>>  *From:* SG  
>>> *Sent:* Monday, July 16, 2012 3:42 AM
>>> *To:* excel-macros@googlegroups.com 
>>> *Subject:* $$Excel-Macros$$ Re: Error Cleaning
>>>  
>>>  Sorry for Replying late.Yes, i choose delimit & choose comma for 
>>> it.Problem is this that the data is in thousandsr rows & manually it takes 
>>> so much time.i have attached a file & i hope it clears the doubts brfore 
>>> cleaning the data & after cleaning the data.
>>>
>>> On Friday, July 13, 2012 5:33:35 PM UTC+5:30, SG wrote:
>>>
>>>> Hi Experts,
>>>>  
>>>> I need your help again.We extract the data from database & each user 
>>>> got some errors on different projects.I need to clean the error data to 
>>>> get 
>>>> the count of errors.For this,i do text to column on error column.Then, in 
>>>> each column after error column, i take the value one by one & copy paste 
>>>> the data below the original data.It's too time consuming as data may 
>>>> expand 
>>>> up to many columns & each column has irregular no. of errors description.I 
>>>> have tried to explain best & need a macro to make unique row of erros of 
>>>> all users.I have attached the excel file & a document in which i have 
>>>> tried 
>>>> to explain.Looking forward for your help.
>>>>  
>>>> Thanks in advance.
>>>>
>>> -- 
>>> FORUM RULES (986+ members already BANNED for violation)
>>>  
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please 
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice 
>>> will not get quick attention or may not be answered.
>>>  
>>> 2) Don't post a question in the thread of another member.
>>>  
>>> 3) Don't post questions regarding breaking or bypassing any security 
>>> measure.
>>>  
>>> 4) Acknowledge the responses you receive, good or bad.
>>>  
>>> 5) Cross-promotion of, or links to, forums comp

Re: $$Excel-Macros$$ Re: Error Cleaning

[dguillett1]

You might try expanding column C to find out that that is NOT 
true

Don Guillett
Microsoft Excel Developer
SalesAid Software
dguille...@gmail.com

From: SG 
Sent: Monday, July 16, 2012 10:26 AM
To: excel-macros@googlegroups.com 
Subject: Re: $$Excel-Macros$$ Re: Error Cleaning

Yes, if you see..from row 12 to row 40, the data is blank..can it be possible 
after running the macro...it would consolidated without such blanks in the 
data??
On Monday, July 16, 2012 8:31:55 PM UTC+5:30, Don Guillett wrote: 


  Don Guillett
  Microsoft Excel Developer
  SalesAid Software
  dguille...@gmail.com

  From: SG 
  Sent: Monday, July 16, 2012 9:30 AM
  To: excel-macros@googlegroups.com 
  Subject: Re: $$Excel-Macros$$ Re: Error Cleaning

  Hi Don,

  It's working perfectly.but one more problem is this i have shown only 3 
columns in my sample file but in real ...i have atleast 23 columns & last 
column is the Error column on which this macro will work...now this macro is 
consolidating the data at the end.Please have a look.For instance, i have 
increased the column no to 5.

  On Monday, July 16, 2012 7:32:46 PM UTC+5:30, Don Guillett wrote:
I wrote it to continue what you had already done. The TTC could be 
incorporated. 
Option explicit forces use of dim
Look in vba help index for resize

Don Guillett
Microsoft Excel Developer
SalesAid Software
dguille...@gmail.com

From: SG 
Sent: Monday, July 16, 2012 8:49 AM
To: excel-macros@googlegroups.com 
Subject: Re: $$Excel-Macros$$ Re: Error Cleaning

Hi Don,

Thanks for the help. I haven't run the macro.I'm a begineer to the 
macro.Can you please explain the "option Explicit" & use of "resize" & would i 
run this macro after text to column splitting?

uillett wrote:
  Should do it

  Option Explicit
  Sub getlists()
  Dim lr As Long
  Dim i As Long
  Dim dr As Long
  Dim btc As Range
  lr = Cells(Rows.Count, 1).End(xlUp).Row - 1
  Set btc = Cells(2, 1).Resize(lr, 2)
  For i = 2 To lr + 1
  dr = Cells(Rows.Count, 1).End(xlUp).Row + 1
  btc.Copy Cells(dr, 1)
  Cells(2, i + 1).Resize(lr).Copy Cells(dr, "c")
  'MsgBox i
  Next i
  Columns(3).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  End Sub
  ‘’
  Don Guillett
  Microsoft Excel Developer
  SalesAid Software
  dguille...@gmail.com

  From: SG 
  Sent: Monday, July 16, 2012 3:42 AM
  To: excel-macros@googlegroups.com 
  Subject: $$Excel-Macros$$ Re: Error Cleaning

  Sorry for Replying late.Yes, i choose delimit & choose comma for 
it.Problem is this that the data is in thousandsr rows & manually it takes so 
much time.i have attached a file & i hope it clears the doubts brfore cleaning 
the data & after cleaning the data.

  On Friday, July 13, 2012 5:33:35 PM UTC+5:30, SG wrote:
Hi Experts,

I need your help again.We extract the data from database & each user 
got some errors on different projects.I need to clean the error data to get the 
count of errors.For this,i do text to column on error column.Then, in each 
column after error column, i take the value one by one & copy paste the data 
below the original data.It's too time consuming as data may expand up to many 
columns & each column has irregular no. of errors description.I have tried to 
explain best & need a macro to make unique row of erros of all users.I have 
attached the excel file & a document in which i have tried to explain.Looking 
forward for your help.

Thanks in advance.
  -- 
  FORUM RULES (986+ members already BANNED for violation)
   
  1) Use concise, accurate thread titles. Poor thread titles, like Please 
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will 
not get quick attention or may not be answered.
   
  2) Don't post a question in the thread of another member.
   
  3) Don't post questions regarding breaking or bypassing any security 
measure.
   
  4) Acknowledge the responses you receive, good or bad.
   
  5) Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 
   
  NOTE : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
   
  
--
  To post to this group, send email to excel-macros@googlegroups.com
   
  To unsubscribe, send a blank email to 
excel-macros+unsubscr...@googlegroups.com
-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please 
Help, Urgent, Need 

Re: $$Excel-Macros$$ Re: Error Cleaning

[SG]

Hi Don,
 
My question is that after running the macro why data from column A to C 
is repeating & then the data from column A to E comes consolidated below it 
which actually i want.Sorry for asking so much questions.But till the time 
it's not clear to meI'm not able to implement it.

On Monday, July 16, 2012 9:03:57 PM UTC+5:30, Don Guillett wrote:

>   You might try expanding column C to find out that that is NOT 
> true
>  
> Don Guillett
> Microsoft Excel Developer
> SalesAid Software
> dguille...@gmail.com
>   
>  *From:* SG  
> *Sent:* Monday, July 16, 2012 10:26 AM
> *To:* excel-macros@googlegroups.com 
> *Subject:* Re: $$Excel-Macros$$ Re: Error Cleaning
>  
> Yes, if you see..from row 12 to row 40, the data is blank..can it be 
> possible after running the macro...it would consolidated without such 
> blanks in the data??
> On Monday, July 16, 2012 8:31:55 PM UTC+5:30, Don Guillett wrote: 
>>
>>
>>  
>> Don Guillett
>> Microsoft Excel Developer
>> SalesAid Software
>> dguille...@gmail.com
>>   
>>  *From:* SG  
>> *Sent:* Monday, July 16, 2012 9:30 AM
>> *To:* excel-macros@googlegroups.com 
>> *Subject:* Re: $$Excel-Macros$$ Re: Error Cleaning
>>  
>>  Hi Don,
>>  
>> It's working perfectly.but one more problem is this i have shown only 3 
>> columns in my sample file but in real ...i have atleast 23 columns & last 
>> column is the Error column on which this macro will work...now this macro 
>> is consolidating the data at the end.Please have a look.For instance, i 
>> have increased the column no to 5.
>>
>> On Monday, July 16, 2012 7:32:46 PM UTC+5:30, Don Guillett wrote:
>>
>>>   I wrote it to continue what you had already done. The TTC could be 
>>> incorporated. 
>>> Option explicit forces use of dim
>>> Look in vba help index for resize
>>>  
>>> Don Guillett
>>> Microsoft Excel Developer
>>> SalesAid Software
>>> dguille...@gmail.com
>>>   
>>>  *From:* SG  
>>> *Sent:* Monday, July 16, 2012 8:49 AM
>>> *To:* excel-macros@googlegroups.com 
>>> *Subject:* Re: $$Excel-Macros$$ Re: Error Cleaning
>>>  
>>>  Hi Don,
>>>  
>>> Thanks for the help. I haven't run the macro.I'm a begineer to the 
>>> macro.Can you please explain the "option Explicit" & use of "resize" & 
>>> would i run this macro after text to column splitting?
>>>
>>> uillett wrote:
>>>
>>>>   Should do it
>>>>  
>>>> Option Explicit
>>>> Sub getlists()
>>>> Dim lr As Long
>>>> Dim i As Long
>>>> Dim dr As Long
>>>> Dim btc As Range
>>>> lr = Cells(Rows.Count, 1).End(xlUp).Row - 1
>>>> Set btc = Cells(2, 1).Resize(lr, 2)
>>>> For i = 2 To lr + 1
>>>> dr = Cells(Rows.Count, 1).End(xlUp).Row + 1
>>>> btc.Copy Cells(dr, 1)
>>>> Cells(2, i + 1).Resize(lr).Copy Cells(dr, "c")
>>>> 'MsgBox i
>>>> Next i
>>>> Columns(3).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
>>>> End Sub
>>>> ‘’
>>>> Don Guillett
>>>> Microsoft Excel Developer
>>>> SalesAid Software
>>>> dguille...@gmail.com
>>>>   
>>>>  *From:* SG  
>>>> *Sent:* Monday, July 16, 2012 3:42 AM
>>>> *To:* excel-macros@googlegroups.com 
>>>> *Subject:* $$Excel-Macros$$ Re: Error Cleaning
>>>>  
>>>>  Sorry for Replying late.Yes, i choose delimit & choose comma for 
>>>> it.Problem is this that the data is in thousandsr rows & manually it takes 
>>>> so much time.i have attached a file & i hope it clears the doubts brfore 
>>>> cleaning the data & after cleaning the data.
>>>>
>>>> On Friday, July 13, 2012 5:33:35 PM UTC+5:30, SG wrote:
>>>>
>>>>> Hi Experts,
>>>>>  
>>>>> I need your help again.We extract the data from database & each user 
>>>>> got some errors on different projects.I need to clean the error data to 
>>>>> get 
>>>>> the count of errors.For this,i do text to column on error column.Then, in 
>>>>> each column after error column, i take the value one by one & copy paste 
>>>>> the data below the original data.It's too time consuming as data may 
>>>>

Re: $$Excel-Macros$$ Re: Error Cleaning

[dguillett1]

You will have to SHOW me in the file

Don Guillett
Microsoft Excel Developer
SalesAid Software
dguille...@gmail.com

From: SG 
Sent: Tuesday, July 17, 2012 4:54 AM
To: excel-macros@googlegroups.com 
Subject: Re: $$Excel-Macros$$ Re: Error Cleaning

Hi Don,

My question is that after running the macro why data from column A to C is 
repeating & then the data from column A to E comes consolidated below it which 
actually i want.Sorry for asking so much questions.But till the time it's not 
clear to meI'm not able to implement it.

On Monday, July 16, 2012 9:03:57 PM UTC+5:30, Don Guillett wrote:
  You might try expanding column C to find out that that is NOT 
true

  Don Guillett
  Microsoft Excel Developer
  SalesAid Software
  dguille...@gmail.com

  From: SG 
  Sent: Monday, July 16, 2012 10:26 AM
  To: excel-macros@googlegroups.com 
  Subject: Re: $$Excel-Macros$$ Re: Error Cleaning

  Yes, if you see..from row 12 to row 40, the data is blank..can it be possible 
after running the macro...it would consolidated without such blanks in the 
data??
  On Monday, July 16, 2012 8:31:55 PM UTC+5:30, Don Guillett wrote: 


Don Guillett
Microsoft Excel Developer
SalesAid Software
dguille...@gmail.com

From: SG 
Sent: Monday, July 16, 2012 9:30 AM
To: excel-macros@googlegroups.com 
Subject: Re: $$Excel-Macros$$ Re: Error Cleaning

Hi Don,

It's working perfectly.but one more problem is this i have shown only 3 
columns in my sample file but in real ...i have atleast 23 columns & last 
column is the Error column on which this macro will work...now this macro is 
consolidating the data at the end.Please have a look.For instance, i have 
increased the column no to 5.

On Monday, July 16, 2012 7:32:46 PM UTC+5:30, Don Guillett wrote:
  I wrote it to continue what you had already done. The TTC could be 
incorporated. 
  Option explicit forces use of dim
  Look in vba help index for resize

  Don Guillett
  Microsoft Excel Developer
  SalesAid Software
  dguille...@gmail.com

  From: SG 
  Sent: Monday, July 16, 2012 8:49 AM
  To: excel-macros@googlegroups.com 
      Subject: Re: $$Excel-Macros$$ Re: Error Cleaning

  Hi Don,

  Thanks for the help. I haven't run the macro.I'm a begineer to the 
macro.Can you please explain the "option Explicit" & use of "resize" & would i 
run this macro after text to column splitting?

  uillett wrote:
Should do it

Option Explicit
Sub getlists()
Dim lr As Long
Dim i As Long
Dim dr As Long
Dim btc As Range
lr = Cells(Rows.Count, 1).End(xlUp).Row - 1
Set btc = Cells(2, 1).Resize(lr, 2)
For i = 2 To lr + 1
dr = Cells(Rows.Count, 1).End(xlUp).Row + 1
btc.Copy Cells(dr, 1)
Cells(2, i + 1).Resize(lr).Copy Cells(dr, "c")
'MsgBox i
Next i
Columns(3).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
‘’
Don Guillett
Microsoft Excel Developer
SalesAid Software
dguille...@gmail.com

From: SG 
Sent: Monday, July 16, 2012 3:42 AM
        To: excel-macros@googlegroups.com 
Subject: $$Excel-Macros$$ Re: Error Cleaning

Sorry for Replying late.Yes, i choose delimit & choose comma for 
it.Problem is this that the data is in thousandsr rows & manually it takes so 
much time.i have attached a file & i hope it clears the doubts brfore cleaning 
the data & after cleaning the data.

On Friday, July 13, 2012 5:33:35 PM UTC+5:30, SG wrote:
  Hi Experts,

  I need your help again.We extract the data from database & each user 
got some errors on different projects.I need to clean the error data to get the 
count of errors.For this,i do text to column on error column.Then, in each 
column after error column, i take the value one by one & copy paste the data 
below the original data.It's too time consuming as data may expand up to many 
columns & each column has irregular no. of errors description.I have tried to 
explain best & need a macro to make unique row of erros of all users.I have 
attached the excel file & a document in which i have tried to explain.Looking 
forward for your help.

  Thanks in advance.
-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please 
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will 
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security 
measure.
 
4)