PDA

View Full Version : find and compare



gcuc1
06-07-2010, 04:39 PM
I have a big long list of data. i need to search for a specific string in chunks of X amounts. In each chuck i need to find all of the strings in column A. then i need to compare its data, column B-Z, with all of the others. I can't quite figure out how to code it in vba. All of the data in column A is a title. Then columns B and on have the actual data for that title. if they don't all have the same data, i need to output an error and say whats wrong.

Thanks

mdmackillop
06-08-2010, 05:25 AM
Welcome to VBAX
Can you post a sample workbook. Use Manage Attachments in the Go Advanced reply section.

gcuc1
06-08-2010, 12:32 PM
Sample data: each color represents each "section" i will work with. its not normally colored, i have other ways of parsing that data. I just need it so i can compare each "control" line with each other for each section. There will be more control samples and more data overall but i have just a small sample for this forum. I couldn't figure out a way to do this efficiently than just two for loops checking everything. i also had trouble getting it to compare.
I currently have variable ints to keep track of where the first group starts, usually row 6, and ends. also each section is a set number that i have input previously.
Thanks for any help!

mdmackillop
06-09-2010, 03:14 PM
I can't follow what is to be searched for and compared. What is OK and whats is error. Can you add some explanation, to your sample.

gcuc1
06-09-2010, 05:54 PM
sorry for the confusion. the data is broken up into a few sections. The colors for the example only show the sections and the sections are always 384 rows. I have an integer variable already showing how many sections to work with. In each section, i need to find each Control line, and compare them to the rest of the control lines in the section only. so for the first section, line 11 needs to compare with line 16 and any other Control lines. I need to compare all of those controls to see if they match. if they don't, i need to error and display where the error is. Then i need to repeat that for each section ( in this example data, the red text is the second section). I was trying to work with match but wasn't getting good results. I also was having errors with converting ranges and moving between ranges.

shrivallabha
06-10-2010, 06:29 AM
I assumed following:

First Row that had "control" is the correct row.

Place it in the standard module and see if the results are satisfactory. Keep in mind that I'm learning the ropes here so this may not be the best possible macro routine.

Sub CheckError()
Dim StartRow As Long
Dim LastRow As Long
Dim SectionRow As Long
Dim ValCheck As String
Dim FirstAddress As Range
Dim Control As String

StartRow = Sheets("Raw Data").Range("A1").End(xlDown).Row + 1
LastRow = Sheets("Raw Data").Range("A65536").End(xlUp).Row
CountLoop = ((LastRow - StartRow) + 1) / 384

For j = 1 To CountLoop
SectionRow = StartRow + 383
For i = StartRow To SectionRow
If Cells(i, 1).Value = "control" Then
Cells(i, 9).FormulaR1C1 = "=CONCATENATE(RC[-7],RC[-6],RC[-5],RC[-4],RC[-3],RC[-2],RC[-1])"
ValCheck = Cells(i, 9).Value
End If
Set FirstAddress = Range("A" & i)
If FirstAddress = "control" Then
For k = FirstAddress.Row + 1 To SectionRow
If Cells(k, 1).Value = "control" Then
Cells(k, 9).FormulaR1C1 = _
"=CONCATENATE(RC[-7],RC[-6],RC[-5],RC[-4],RC[-3],RC[-2],RC[-1])"
If Cells(k, 9).Value <> ValCheck Then
Cells(k, 10).Value = "Error"
Else
Cells(k, 10).Value = "OK"
End If
End If
Next k
i = k
End If
Next i
StartRow = SectionRow
Next j
End Sub

Hope this helps