Copy data from one sheet to another and concat and sort data

%3CLINGO-SUB%20id%3D%22lingo-sub-870118%22%20slang%3D%22en-US%22%3ECopy%20data%20from%20one%20sheet%20to%20another%20and%20concat%20and%20sort%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-870118%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20excel%20workbook%20as%20attached.%20This%20is%20sample%20only.%26nbsp%3B%20I%20want%20to%20automate%20the%20below%20task%20using%20VBA.%3C%2FP%3E%3CP%3EI%20have%20requirement%20like%20below%2C%20would%20highly%20appreciate%20any%20help%20with%20the%20below%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EConsideration%3A%20There%20might%20be%20cells%20which%20are%20blank%2C%20but%20the%20operation%20should%20not%20count%20blank%20cells%20as%20the%20end%20of%20data.%3CBR%20%2F%3EThe%20sheet%20names%20will%20be%20different%20each%20time%2C%20but%20we%20always%20need%20to%20do%20the%20data%20manipulation%20in%20sheet%202%20and%204.%3C%2FP%3E%3CP%3E1.%20On%20Test%20Report%20Sheet%3A%20Paste%20values%20only%20from%20columns%20C%2CD%2CE%2CF%20from%20Lab%20Test%20Sheet%20to%20Test%20Report%20Sheet%20(%20including%20column%20header).%3CBR%20%2F%3E2.%20On%20the%20Test%20Report%20Sheet%3A%20At%20Column%20E%20on%20cell%20E2%2C%20put%20formula%20%3DCONCAT(A2%2CB2%2CC2%2CD2)%20and%20copy%20the%20formula%20to%20all%20cells.%3CBR%20%2F%3E3.%20On%20the%20Test%20Report%20Sheet%3A%20Copy%20only%20the%20values%20in%20column%20E%20to%20column%20F.%3CBR%20%2F%3E4.%20On%20Test%20Report%20Sheet%3A%20Sort%20Column%20F(%20A-Z)%20with%20expand%20selection%20option.%3C%2FP%3E%3CP%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3C%2FP%3E%3CP%3E1.%20On%20Export%20Report%20Sheet%3A%20Paste%20values%20only%20from%20Columns%20F%2CC%2CE%2CD%20from%20Export%20Sheet%20to%20Export%20Report%20Sheet%20(%20including%20column%20header).%3CBR%20%2F%3E2.%20On%20the%20Export%20Report%20Sheet%3A%20At%20Column%20E%20on%20cell%20E2%2C%20put%20the%20formula%20%3DCONCAT(A2%2CB2%2CC2%2CD2)%20and%20copy%20the%20formula%20to%20all%20cells.%3CBR%20%2F%3E3.%20On%20the%20Export%20Report%20Sheet%3A%20Copy%20only%20the%20values%20in%20column%20E%20to%20column%20F%3CBR%20%2F%3E4.%20On%20the%20Export%20Report%20Sheet%3A%20Sort%20column%20F%20(A-Z)%20with%20expand%20selection%20option.%3C%2FP%3E%3CP%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3C%2FP%3E%3CP%3ECompare%2FMatch%20Column%20F%20in%20Test%20Report%20Sheet%20and%20Column%20F%20in%20Export%20Report%20Sheet%20and%20highlight%20the%20entire%20row%20with%20different%20values%20with%3CBR%20%2F%3Esome%20colour.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-870118%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-871918%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%20data%20from%20one%20sheet%20to%20another%20and%20concat%20and%20sort%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-871918%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20appreciate%20help%20on%20the%20above%20requirement.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-879063%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%20data%20from%20one%20sheet%20to%20another%20and%20concat%20and%20sort%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-879063%22%20slang%3D%22en-US%22%3E%3CP%3EAny%20advice%20on%20the%20above%20requirement%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

I have excel workbook as attached. This is sample only.  I want to automate the below task using VBA.

I have requirement like below, would highly appreciate any help with the below:

 

Consideration: There might be cells which are blank, but the operation should not count blank cells as the end of data.
The sheet names will be different each time, but we always need to do the data manipulation in sheet 2 and 4.

1. On Test Report Sheet: Paste values only from columns C,D,E,F from Lab Test Sheet to Test Report Sheet ( including column header).
2. On the Test Report Sheet: At Column E on cell E2, put formula =CONCAT(A2,B2,C2,D2) and copy the formula to all cells.
3. On the Test Report Sheet: Copy only the values in column E to column F.
4. On Test Report Sheet: Sort Column F( A-Z) with expand selection option.

==============

1. On Export Report Sheet: Paste values only from Columns F,C,E,D from Export Sheet to Export Report Sheet ( including column header).
2. On the Export Report Sheet: At Column E on cell E2, put the formula =CONCAT(A2,B2,C2,D2) and copy the formula to all cells.
3. On the Export Report Sheet: Copy only the values in column E to column F
4. On the Export Report Sheet: Sort column F (A-Z) with expand selection option.

===============================================================

Compare/Match Column F in Test Report Sheet and Column F in Export Report Sheet and highlight the entire row with different values with
some colour.

 

2 Replies
Highlighted

 

Would appreciate help on the above requirement.

Highlighted

Any advice on the above requirement?