Home

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
Sam789
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

 

Would appreciate help on the above requirement.

Any advice on the above requirement?

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies