Excel 2003: Copy Only Cells with Conditional Formatting to Another Worksheet

[L_A_Johnson]

EXCEL VERSION: Excel 2003 under Windows XP

GOAL: Download order entry field comparison report from accounting system and import into Excel. Present a list of only the exceptions for processing by the data entry team.

DETAILS:
We have two separate order record systems, one for the business group and one for the data entry team. Our order orthodoxy officer runs continuous reports comparing the data in the two systems, but must wade through many fields to get to the occasional exception that needs to be fixed. Each order can have up to 26 possible exceptions, and depending on the time period being examined, there could be up several hundred orders to check. I was hoping to be able to provide a consolidated list showing just the exceptions by order number.

HOW FAR I'VE GOTTEN: I download the report from our accounting system. It contains all relevant fields to be compared between the business system and the data entry system. Excel re-organizes the data so that fields in the business and data systems are compared side by side, then identifies the exceptions with a separate cell containing the word "Yes" and with the exception cells themselves conditionally formatted with a yellow background.

NEXT STEP: Copy only the exception cells to a new worksheet. (This is where I'm stuck.)

DATA TABLE LAYOUT:
(I hope this is comprehensible. Dad-blasted HTML spacing!)

COL1 : COL2 : COL3 : COL4 : COL5 : COL6
​

TITLES: Bus_Trade : Data_Trade : Bus_Item : Data_Item : Bus_Vol : Data_Vol

ROW1: 32555 : 32555 : 01 : 01 : 25,000 : 25,000
ROW2: 32610 : 32601 : 01 : 01 : 15,000 : 15,000
ROW3: 32615 : 32615 : 01 : 10 : 7,500 : 7,500
ROW4: 32620 : 32620 : 01 : 01 : 10,000 : 12,000
​

"Bus_" = Business group's system
"Data_" = Data entry group's system
"Trade" = Reference number for order
"Item" = Order item number
"Vol" = Order quantity

In this example, ROW1 has no exceptions; ROW2 shows a difference between "Bus_Trade" and "Data_Trade"; ROW3 shows a difference between "Bus_Item" and "Data_Item"; and ROW4 shows a difference between "Bus_Vol" and "Data_Vol". For ROW1, no cells are conditionally formatted with a yellow background. For ROW2, Columns 1 and 2 are conditionally formatted to yellow. For ROW3, Columns 3 and 4 are conditionally formatted to yellow. For ROW4, Columns 5 and 6 are conditionally formatted to yellow.

The occurrence of exceptions is not predictable. For some orders, the volume and the item numbers may vary. For others, it could be the order number and the item number. For yet another set, it could be that the item numbers and the volume are different.

REQUEST OF MrExcel EXPERTS: Is there a way to get Excel to nab just the cells with the yellow background and copy them to another workbook? I've seen methods to copy an entire row of data, or an entire column of data, but nothing that would select just a miscellaneous set of cells with a particular formatting.

If the conditional formatting won't permit Excel to differentiate between cells to copy and cells to skip, there is an extra column not included in the example above ('cause I ran out of room). That extra column has a "Yes" in it if the row contains an exception between the two compared cells. It would also be possible to use that "Yes" as the differentiator.

Begging your pardon for the LENGTHY layout, but I've been researching this for a couple of months now and have yet to see anything comparable to this particular request. Brilliant ideas welcome; lousy notions welcome; crazy schemes welcome; any responses welcome!

 

[Warship]

Maybe instead of searching for Conditionally Formatted cells, just use VBA to do the comparisons?
Something like should work (untested):

Sub getExceptions()
    Dim r As Long, c As Long
    For r = 2 To Sheets("your_sheetname").Cells(Rows.Count, 1).End(xlUp).Row
        For c = 1 To 5 Step 2
            If Sheets("your_sheetname").Cells(r, c) <> Sheets(1).Cells(r, c + 1) Then
                'copy data to your other workbook here
            End If
        Next c
    Next r
End Sub

 

[Warship]

Long day at the office...

Correction:
If Sheets("your_sheetname").Cells(r, c) <> Sheets(1).Cells(r, c + 1) Then

should be
If Sheets("your_sheetname").Cells(r, c) <> Sheets("your_sheetname").Cells(r, c + 1) Then

 

[crook_101]

Hi,

Yellow cell fill solution - highlight the headers in yellow fill as below:

Excel Workbook
	A	B	C	D	E	F
1	Bus_Trade	Data_Trade	Bus_Item	Data_Item	Bus_Vol	Data_Vol
2	32555	32555	1	1	25000	25000
3	32610	32601	1	1	15000	15000
4	32615	32615	1	10	7500	7500
5	32620	32620	1	1	10000	12000
Sheet4 (2)

Control & F to bring up the Find & Replace box
Use the "Format" drop down and select "Choose Format from cell"
Click on an unfilled cell, in eg above F3
Click on replace but do not enter anything in the box
Hit replace all and you will get the below:

Excel Workbook
	A	B	C	D	E	F
1	Bus_Trade	Data_Trade	Bus_Item	Data_Item	Bus_Vol	Data_Vol
2
3	32610	32601
4			1	10
5					10000	12000
Sheet4 (2)

Hit F5 - GoTo>>Special>>Blanks>>OK

Right click over one of the highlighted cells Delete>>Shift cells up>>OK
You should be left with the below:

Excel Workbook
	A	B	C	D	E	F
1	Bus_Trade	Data_Trade	Bus_Item	Data_Item	Bus_Vol	Data_Vol
2	32610	32601	1	10	10000	12000
Sheet4 (2)

Long winded, yes but it works

Ian,

 

[L_A_Johnson]

Thanks, you guys! No real progress to report just yet, but this has given me some grand ideas and I'm starting to fiddle. I'll be sure to let y'all know what we all come up with.

In the meantime, smoochies, and have a great day/week/month/however long it takes me to get this figured out!