Excell - sorting the records

[blazblaz]

I have a data of few thousands rows where the number can be in D,E column, or in F,G (but not in both DE, and FG columns in same row)

A- Group the part belongs (total 4 groups, group-1, group-2, group-3, group-4)
B- Part ident #
C-Date and time when the measurements happened.
D-Measured Dimension-1
E-Measured Dimension-2
F-Measured Dimension-1
G-Measured Dimension-2

A B C D E F G

1 Gr-1 ID1 Date-1 2 4
2 Gr-1 ID2 Date-2 2 1
3 Gr-2 ID3 Date-3 3 4
4 Gr-3 ID4 Date-4 2 4
5 Gr-1 ID5 Date-5 2 2
.
.
25,000 Gr-4 ID24300 Date-4 3 2

My task is:
1) I want to create a new sheet where I can have just 2 columns for measured dimensions, where the data CD, or EF, will be in some new, assume GH columns.
2)The data in column B the part identification possible repeats, the different is the date & time Column C) when it happened. I want to have a unique list, where the part # appears just ones, and that which happened latest, based on the time .
3)I want to make some reports about some average measured dimensions for each group of the parts (Total 4 groups, based on column A) Do I need to make separate sheets for each group, or just to use 1 sheet, and use the Countif function?

Because the users of this program not on high level to handle excel, is it possible to have my first sheet already programmed with final report, and they just need to paste in the second sheet new records ? So pasting some 20,000 records in the first sheet will automatically calculate required values for each group of parts, ( like how many records occurred for already defined ranges of dimensions a)1-3 b) 3-5 c)5-10 etc)
Is this requires VBA programming?

I’d appreciate if somebody can give me some instruction

Les

 

[KenWright]

One way perhaps:-

1) I'm assuming you got your columns mixed up and that when you say CD or EF becoming GH, you really meant DE or FG becoming HI. Assuming I am correct, simply copy Columns D:G and paste into Col H, so you end up with your data repeated in cols H:K. Now select Cols H:I, do Edit / Go To / Special / Blanks and then do Edit / Delete / Shift cells Left. This puts all your data into just columns H and I.

2 and on - Every other step you mention sounds to me like it could be done with a Pivot table very easily indeed. Assuming ALL your columns have headings, select all your data, do Data / Pivot table and Chart report / next / next / finish. From here drag your Part ID field into the Row fields, then drag the Time&Date field literally just to the right of it (NOT into the Data field), Drag the headers for Columns H & I each into The DATA field, and then click hold and drag the grey buttom marked Data that just appeared on your sheet up into the COLUMN fields area of the Pivot table.

The pivot table route would make automation easy with the sole exception of the step where you combined all data into just two columns. This could be done with formulas, but on 20,000 records plus you may well see some overhead in terms of calculation speed etc.

Regards
Ken....................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------