Re-arranging data

[yorkuvir]

Hello folks,

https://ibb.co/kZZ2Qv

As in the above figure, I have a worksheet containing rows that looks like the left side of the figure, and I wish to sort them out to look like the right side of the figure. Please note, the Goods referred to in the subsequent rows are different, say, apples and oranges respectively, so I cannot merge them or apply filters to them.

What is the shortest way I can accomplish this?

Thanks.

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG27May11
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Ray = Range("C4", Range("c" & Rows.Count).End(xlUp)).Resize(, 2)
ReDim nray(1 To UBound(Ray), 1 To 3)
c = 1
[COLOR="Navy"]For[/COLOR] n = 1 To UBound(Ray) [COLOR="Navy"]Step[/COLOR] 3
    [COLOR="Navy"]If[/COLOR] n = 1 [COLOR="Navy"]Then[/COLOR] nray(1, 1) = Ray(1, 1): nray(1, 2) = Ray(2, 1): nray(1, 3) = Ray(3, 1)
        c = c + 1
    nray(c, 1) = Ray(n, 2)
    nray(c, 2) = Ray(n + 1, 2)
    nray(c, 3) = Ray(n + 2, 2)
[COLOR="Navy"]Next[/COLOR] n
Range("G4").Resize(c, 3).Value = nray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[yorkuvir]

Thank You, Mick; I will try that. Regards.

 

[Rick Rothstein]

My data is from c4 to d24 .

I wish the result to go into g4 to i11 OR from g5 to i11, because g4, h4 and i4 can be manually entered too.

B5, B6, c3, d3, h3 and i3 are just extra entries for clarification.
.

To clarify...

1) The only thing in Column B are the numbers, correct?

2) Where you show the word "Goods"... is that what is really in the cells or are the names of actual goods there?

3) If the name of actual goods, are they always in the same order under the word "Received"?

4) Can the number of goods vary in number under each "Received" header (you show two, but is it always two)?

Just so you know, actual representative data rather than a simplified version of data is much preferred because very often what is in the cells guides the solution we provide. So if you show us "fake data", there is a good possibility the solutions we give you won't work with your actual data.

 

[yorkuvir]

@Rick Rothstein:

Thanks for your response.

1) Column B does not need to have anything, I just included it for some anticipated clarification.

2) Yes, the word "Goods" is really in the cells, and not the name of the actual goods. Still, the first goods is different from the second goods. As an example, the first goods represents marks by judge A, while the second goods represents marks by judge B.

3) They are always in the SAME order - that is, marks by judge A always precedes marks by judge B.

4) The number of goods is always constant - only two judges A & B award the marks.

If I have over-complicated, here is the thing I wish to do:

The following lines contain raw text data I extract from a website:

Test-Score 57
Marks by Judge 75
Marks by Judge 27
Test-Score 58
Marks by Judge 25
Marks by Judge 49
Test-Score 50
Marks by Judge 33
Marks by Judge 30
Test-Score 5
Marks by Judge 25
Marks by Judge 18
Test-Score 60
Marks by Judge 30
Marks by Judge 24
Test-Score 67
Marks by Judge 42
Marks by Judge 50
Test-Score 100
Marks by Judge 47
Marks by Judge 24

I wish to categorize them as:

Test-Score Marks by Judge Marks by Judge
57 75 27
58 25 49
50 33 30
5 25 18
60 30 24
67 42 50
100 47 24

The site does not differentiate between Judge A and Judge B while saying 'Marks by Judge'. That is up to me to understand that the first one is by Judge A and the second one is by Judge B.

The format and the wordings do not change ever. Only the values (numbers) change.

My aim is to find the bottom-most 3 values for each of Test-Score, Marks by Judge A & Marks by Judge B. If a row has bottom values of all Test-Score, Marks by Judge A & Marks by Judge B, that is to be rejected.

Hope I am clear?

Thanks for your time.

 

[Rick Rothstein]

For the layout you showed in your latest posted image, put your labels in cells G4:I4, then put this formula in cell G5...

=IF(3*ROW(D1)>=24,"",OFFSET($D$1,3*ROW(D1)+COLUMN(D1)-4,0))

and copy it across to cell I5, then copy those three cells down to Row 10 (or beyond if you want to prepare for different length data in Columns C and D) for future pastings.

 

[Rick Rothstein]

For the layout you showed in your latest posted image, put your labels in cells G4:I4, then put this formula in cell G5...

=IF(3*ROW(D1)>=24,"",OFFSET($D$1,3*ROW(D1)+COLUMN(D1)-4,0))

and copy it across to cell I5, then copy those three cells down to Row 10 (or beyond if you want to prepare for different length data in Columns C and D) for future pastings.

The OFFSET function is Volatile and probably should be avoided when possible. With that in mind, here is a non-Volatile formula that should also work..

=IF(3*ROWS($5:5)>=24,"",INDEX($D:$D,3*ROWS($5:5)+1+COLUMNS($G:G)-1))

 

[yorkuvir]

..... Here is a non-Volatile formula that should also work..

=IF(3*ROWS($5:5)>=24,"",INDEX($D:$D,3*ROWS($5:5)+1+COLUMNS($G:G)-1))

Wow! Fantastic. That worked!

Thanks for the ingenious solution, Mr. Rick Rothstein.

Much appreciated, since you have made my job a lot easier.

 

[Rick Rothstein]

Wow! Fantastic. That worked!

Thanks for the ingenious solution, Mr. Rick Rothstein.

You are quite welcome, but I have a change you need to make in order to keep the code completely self-contained. Here is the formula I gave you to put in cell G5 and then copy across and down...

=IF(3*ROWS($5:5)>=24,"",INDEX($D:$D,3*ROWS($5:5)+1+COLUMNS($G:G)-1))

The main problem with this formula as written is the number I highlighted in red... it must be the row number of the last row with data in it and, as the formula stands now, it must be updated when your data changes. To make this formulas completely automatic, we need to make the formula calculate the last used row number on its own. Unfortunately, the code to do that is resource intensive and including it the main formula so that it is replicated each time the cell is copied across and down would overwhelm your system's resources quite quickly. The way to overcome this problem is to place the formula for calculating the last row in a cell by itself (so that it is executed only once) and replace the red highlighted number above with a reference to that cell. This formula...

=SUMPRODUCT(MAX(ROW(D$1:D$65535)*(D$1:D$65535<>"")))

will calculate the row number for the last used row number in Column D. The two occurrences of 65535 are the last row number beyond which you know you will never have data... these numbers should be as small as you are able to make them to reduce the resources the formula uses to do its calculation; so, if you know you will never have data beyond, say, 2000, then change both 65535 values to 2000. Okay, now you need to put this formula in a cell somewhere... I'll assume cell F5. Now replace the red highlighted 24 in the original formula to $F$5 so that it now looks like this...

=IF(3*ROWS($5:5)>=$F$5,"",INDEX($D:$D,3*ROWS($5:5)+1+COLUMNS($G:G)-1))

then place it in cell G5 and copy it across to cell I5, then copy those three cells down to our assumed last possible data row of 2000. Once you have done that, you will never have to touch Column's G, H or I again... just copy paste your new (fresh) data into the two-column range starting at cell C4 and the results in Columns G, H and I will update automatically.

Now, finally, about that formula I assumed you put in cell F5... if you think it looks "ugly" just sitting out there by itself, Custom Cell Format the cell using two semi-columns (;;) as the Custom Cell Format Type pattern and the number it displays will become invisible (unless someone selects that cell in which case its value would show in the Formula Bar).

 

[yorkuvir]

Thanks again, Rick, for the modification plus elaborate explanation. I have adapted your amendments and it works perfectly.

I have placed your formula in cell K4 and hidden it, as can be seen in the formula bar in the screenshot at https://prnt.sc/fcy79z . (The data would never exceed row 100, and the current data is till row 45.)

If you can tolerate, I would prefer one or two refinements please:

(i) As in the above figure, some Test-Scores are blank (absent for the test), but they show as 0 in the result. How to make them show only blanks instead of having zeroes? Because, for example, if I calculate the average values, they can be misleading if 0's are included in the calculation, instead of just omitting the absentees.

(ii) The cells containing data and results - from D4... and from G5... above - were for example in the initial stages, while the actual data would be from B1... and the actual result would be from F2... (or I may insert a few columns at column D too). When I moved the data to A1... as in figure at https://prnt.sc/fcy7f8 , it is not reflected in the results, and some values are simply excluded. What would be the formula that can accommodate these changes too?

Thanks again.

 

[mole999]

what if a student attends and scores 0