merge and collate data in rows
- Thread starter Clancy
- Start date
Asheesh
Excel Ninja
Welcome on board...
You are not clear about your requirement...
Your data table contains data from column A to F, there is nothing in G to L..
Secondly, you say data in columns M to S doesnt match....but as per your file, there is no data in these columns...
Either you have posted a wrong attachment or you need to explain your requirement in detail...
You are not clear about your requirement...
Your data table contains data from column A to F, there is nothing in G to L..
Secondly, you say data in columns M to S doesnt match....but as per your file, there is no data in these columns...
Either you have posted a wrong attachment or you need to explain your requirement in detail...
NARAYANK991
Excel Ninja
Hi ,
Given that there is very little data and explanation , I doubt that much help will be forthcoming.
If you can explain what is the input data that one starts with , and what is the expected output , and how the output is to be derived from the input , then it is possible that answers will be given.
Narayan
Given that there is very little data and explanation , I doubt that much help will be forthcoming.
If you can explain what is the input data that one starts with , and what is the expected output , and how the output is to be derived from the input , then it is possible that answers will be given.
Narayan
Sudhir Prabhu
Member
If I read the first post, I think by merge you mean, you are trying to find duplicates or multiple rows of same data, some possible partial as in example provided. I suggest you use filters - apply filter on the first row. Select against columns with a given value in each column and quickly do a manual - merge (data copy) - and delete redundant records - simple process.
Sudhir Prabhu
Member
Hi Clancy,
Now that we know the problem statement - (and 400 records is nothing!) if you use a combination of multiple tools - functions, shortcuts, auto highlighters etc within excel, you can do this in a breeze.
In my opinion, a better approach is to have some amount of manual data processing - and hence have an oversight that error prone automation does not end up disregarding important info inadvertently.
here are some tips on 'how to':
- add a helper column to begin with - call it "group"
- use Conditional formatting > highlight cells rules > duplicate values for the columns you know are usual indicators of duplicate / repeated rows. It could be MaintRepairno, or containerno, or may be even clientinvoicecost.
- use auto-filter and filter by colour option
- once you have determined and assured that the filtered set of row data are indeed same and hence should be merged, enter a value say "Group01" in group column for such records.
- If there are many, use shortcut key Ctrl +D to fill the Group column for all those rows (while you are in filter mode)
- unfilter - and repeat above steps as many times as you feel the necessity according to the duplicate highlight across various columns - untill all rows are grouped together in particular groups to be merged
Merging Data
Once you have the groups of rows - the 400 could have reduced to say 50 to 100 only, depending on how many were duplicates - you could use any of the normal function such as concatenate or copy down and then delete multiple records etc. if your data has strange situations - such as different value for same column header under different rows, then use
> Fill > Justify function to merge data into a single cell. (remember to widen the column sufficiently to accommodate merged data, before doing so, else fill will fail)
Finally delete unwanted rows (Ctrl + "-")
I have tried to simulate all steps above
>>PrimData - a copy of your sheet 1, with conditional format attached on 3 columns (my assessment of what could be criteria for duplicates).
>>Step 2: I use filter by colour, and determine the data sub set for duplication. Ascertained that rows 5 and 7 are duplicate; both are tagged as Group01.
>>Step 3: (assuming all rows now grouped) - select groups one by one do a final scrutiny for correct assessment and merge data - either manually or by fill > justify or just delete repeated rows if they are exact duplicate (as in this sample). in the given data, there was nothing to merge - i have simulated by adding "Get" in row one and "Going" in row two under "'5216 - M&R – Wear & Tear"
Select q5 and q7. use Fill > Justify to merge data into the uppermost cell (q5 in this case)
Finally Step 4 - i retain row 5 as "merged data" and delete row 7
hope this helps you with some ideas. Important to remember, Excel is a tool with multiple utilities that you have to creatively use in various permutations according to your variable needs. Only if there is a recurring and steady pattern to such repeated data, you can design a vba code to work seamlessly.
Reg
Sudhir
Now that we know the problem statement - (and 400 records is nothing!) if you use a combination of multiple tools - functions, shortcuts, auto highlighters etc within excel, you can do this in a breeze.
In my opinion, a better approach is to have some amount of manual data processing - and hence have an oversight that error prone automation does not end up disregarding important info inadvertently.
here are some tips on 'how to':
- add a helper column to begin with - call it "group"
- use Conditional formatting > highlight cells rules > duplicate values for the columns you know are usual indicators of duplicate / repeated rows. It could be MaintRepairno, or containerno, or may be even clientinvoicecost.
- use auto-filter and filter by colour option
- once you have determined and assured that the filtered set of row data are indeed same and hence should be merged, enter a value say "Group01" in group column for such records.
- If there are many, use shortcut key Ctrl +D to fill the Group column for all those rows (while you are in filter mode)
- unfilter - and repeat above steps as many times as you feel the necessity according to the duplicate highlight across various columns - untill all rows are grouped together in particular groups to be merged
Merging Data
Once you have the groups of rows - the 400 could have reduced to say 50 to 100 only, depending on how many were duplicates - you could use any of the normal function such as concatenate or copy down and then delete multiple records etc. if your data has strange situations - such as different value for same column header under different rows, then use
> Fill > Justify function to merge data into a single cell. (remember to widen the column sufficiently to accommodate merged data, before doing so, else fill will fail)
Finally delete unwanted rows (Ctrl + "-")
I have tried to simulate all steps above
>>PrimData - a copy of your sheet 1, with conditional format attached on 3 columns (my assessment of what could be criteria for duplicates).
>>Step 2: I use filter by colour, and determine the data sub set for duplication. Ascertained that rows 5 and 7 are duplicate; both are tagged as Group01.
>>Step 3: (assuming all rows now grouped) - select groups one by one do a final scrutiny for correct assessment and merge data - either manually or by fill > justify or just delete repeated rows if they are exact duplicate (as in this sample). in the given data, there was nothing to merge - i have simulated by adding "Get" in row one and "Going" in row two under "'5216 - M&R – Wear & Tear"
Select q5 and q7. use Fill > Justify to merge data into the uppermost cell (q5 in this case)
Finally Step 4 - i retain row 5 as "merged data" and delete row 7
hope this helps you with some ideas. Important to remember, Excel is a tool with multiple utilities that you have to creatively use in various permutations according to your variable needs. Only if there is a recurring and steady pattern to such repeated data, you can design a vba code to work seamlessly.
Reg
Sudhir