CODEWITHSUNDEEP
excelvbadictionaryduplicatesadvanced-search
Ajit Dohaliya
Ajit Dohaliya

Reputation: 11

If two consecutive columns are equal and adjacent column has variations find those entries and populated on a new tab

For Excel, I want to compare all consecutive cells in a column with the values in adjacent column. If there is a variation for same consecutive cells having same values, I want to copy the entire row to a new tab called "incorrect coding error"

Sample for Query

So, in the sample Image, you can see that there are some dupicate entries in "hash" header, and in the adjacent cell having header "Coding" there are coding value for Hash Value. I have colour coded for ease of identification.

For the yellow hilighted (all non responsive coding) and Blue highlighted (all non responsive coding) hash values the "coding" column are same and for Orange/Brown/Grey has values, the codings are different (these cells also highlighted in Red for ease identification - codings where there is variation for same hash values)

For each hash value where codings are different, I would like copy the entire rows (including other headers such as "Control no., Filename or any other header that may be assicicated) to be populated in a new tab by what ever name (say "Coding Error").

Final result shoudl look result like below image:

Solution Image

Please note, on the solution image that even though the AA8F67 hash value had only single row variation, all the hash values including other headers are copied to the new tab as an error.

Another related issue is that the header "Hash" and "Coding" are not same for all data that I receive, so I would really appreciate if I could manually input these headers for any variations within VBA itself - so that I can modify as per requirement. So whatever VBA solution there might be, it should be based on headers, rather than column no.

As I am just new to VBA, I havent found any solution but optimistic that I should be able to find some solution here

Upvotes: 1

Views: 57

Answers (1)

Sunny
Sunny

Reputation: 98

Try this, I made VBA Code just filter "RED" colour from Column "Coding", and cut it in a new tab

idk if this helps you or not, i hope helps u

Sub Macro1()


Dim Nwb As Workbook
Dim Nsh As Worksheet
Dim S1 As Worksheet

Set S1 = ThisWorkbook.Sheets("Sheet1")   ' Change with ur Sheet Name


S1.Range("$A$1:$D$15").AutoFilter Field:=4, Criteria1:=RGB(255, 0 _
    , 0), Operator:=xlFilterCellColor


Set Nwb = Workbooks.Add
Set Nsh = Nwb.Sheets(1)

S1.Range("A1").CurrentRegion.Copy Nsh.Range("A1")
Nsh.UsedRange.EntireColumn.ColumnWidth = 15

S1.Select
S1.Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).EntireRow.Delete

If S1.AutoFilterMode Then S1.AutoFilter.ShowAllData

MsgBox "Done"
 End Sub

enter image description here

Upvotes: 0

Related Questions