Data alignment via macro - help needed

[inactiveuserps19]

Sorry if something like this has been asked before - my knowledge is so minimal I wouldn't even be able to recognise a similar problem!

I'm struggling to create a macro to do something fairly complicated. I have a spreadsheet with various sheets and a lot of data, and what I want to do is this:

1) Look at row 2 of sheet 1.
2) Find the first value that is greater than x (call that cell X)
3) Align that row so that X is in column Y.
4) Re-align the corresponding row by the same amount in sheet 2 (I suspect this is the hardest part)
5) Repeat 1) to 4) for every row in sheets 1 and 2.


I'd be extremely grateful for any help!

 

[MickG]

You are more likely to get a speedy answer if you show an Example of your Data both :- "Before" And "After"

 

[inactiveuserps19]

Thanks.

Okay, so this is an example of sheet 1, before (and let's say x = 20)

02 02 04 03 04 05 09 12 15 20 25 27 28
... 03 06 05 03 06 06 08 25 23 30 35
.........05 07 07 09 13 15 19 26 30

And this is sheet 2:

03 04 03 05 04 05 06 04 08 07 09 08 06
... 05 06 04 05 03 07 06 05 04 05 04
........ 06 06 07 05 08 06 06 05 06


Afterwards, sheet 1:

02 02 04 03 04 05 09 12 15 20 25 27 28
........ 03 06 05 03 06 06 08 25 23 30 35
.....05 07 07 09 13 15 19 19 26

And sheet 2:

03 04 03 05 04 05 06 04 08 07 09 08 06
........ 05 06 04 05 03 07 06 05 04 05 04
.... 06 06 07 05 08 06 06 05 06

 

[inactiveuserps19]

I should also add - the rows will not all have the same number of columns. HOWEVER, corresponding rows in each sheet will, i.e. row 1 in sheet 1 is the same length as row 1 in sheet 2, but NOT the same as row 2 in sheet 1.

 

[MickG]

Looking in the 2nd row of sheet (1) for number > 20 , we find "25",
This 2nd row is then Moved right so that "25" is aligned under "20" (in the first row) which I assume is column "Y".
How do we know which column is which????
In Row (3) the Number >20 which is "26" is already alignd with "20" in the first row, which I take again to be column "Y", so Does not need to move.
In Sheet(2) Row (3) there is a "19" missing that is in sheet(1) , and the numbers on the left of number "26" have been moved to the right.
Can you explain ??
Or maybe I'm looking at it incorrectly !!!

 

[inactiveuserps19]

Sorry, my example was really bad. I've no idea where that missing 19 disappeared to! It will have been a typo by me. I should have said greater than or equal to - so in line 1, '20' would be the qualifying value. Finally, in the real thing, row 1 would be full of column titles and the likes, hence why it needs to start at row 2. My sample values didn't have that row 1. I'll try to post something a bit more useful!

All the mistakes you've noticed suggest that you have actually got the right idea about what I need. It doesn't really matter which column 'Y' is - I should be able to edit that part of the macro myself, but for argument's sake let's say it's column CM. So in row 2, the first value of 20 or above is shifted to column CM, and all the surrounding data move with it. Then the same for every row, right down to the end. And also the corresponding rows in sheet 2 move the same amount.

 

[inactiveuserps19]

Okay, here's a before and after (or 'during', I suppose, as I've only moved the first few lines). The criteria here is '10', not '20', but the principle is the same. The cell colouring is to make it easier to see what's going on - it's not present in the real thing.

The other thing I haven't shown is that every corresponding row in sheet 2 ('IHCR' is the actual sheet name) needs to be shifted by the same amount. (For what it's worth, these numbers correspond to force, and '20' is an absolute threshold value for force that I want these responses aligned by. As they currently are, they're aligned as they were recorded - based on a velocity of change in voltage.)


Before:


After:



Thanks again for any help anyone can offer!

 

[inactiveuserps19]

Any ideas?

 

[inactiveuserps19]

Anyone?

 

[MickG]

Try this:-
NB:- Enter Alignment column and number as 26/15
The Column number chosen (See code) must be less then the column of the Number Chosen:-.i.e the rows will only move to the Right.

[COLOR=navy]Sub[/COLOR] MG21Jun37
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] RngAc [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Ac [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Col [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] Num [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] Message, Title, Default, MyValue
[COLOR=navy]Dim[/COLOR] P1 [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] Temp
[COLOR=navy]Dim[/COLOR] nRng [COLOR=navy]As[/COLOR] Range
'[COLOR=green][B] This means the column(26) and the Aligning number is  "15"[/B][/COLOR]
Message = "Enter values as Align,Column/Number i.e. 26/15"
Title = "Align Rows"
Default = ""
MyValue = InputBox(Message, Title, Default)
Col = Split(MyValue, "/")(0)
Num = Split(MyValue, "/")(1)
[COLOR=navy]With[/COLOR] Sheets("sheet5")
    [COLOR=navy]Set[/COLOR] Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]End[/COLOR] With
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    [COLOR=navy]Set[/COLOR] RngAc = Range(Range("A" & Dn.Row), Cells(Dn.Row, Columns.Count).End(xlToLeft))
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Ac [COLOR=navy]In[/COLOR] RngAc
    [COLOR=navy]If[/COLOR] Ac >= Num [COLOR=navy]Then[/COLOR] P1 = Ac.Column: [COLOR=navy]Exit[/COLOR] For
[COLOR=navy]Next[/COLOR] Ac
    [COLOR=navy]If[/COLOR] P1 <= Col [COLOR=navy]Then[/COLOR]
        Temp = Application.Transpose(Application.Transpose(RngAc.Value))
        Dn.EntireRow.ClearContents
        [COLOR=navy]Set[/COLOR] nRng = Dn.Offset(, Col - P1).Resize(, UBound(Temp))
        nRng = Temp
        [COLOR=navy]With[/COLOR] Sheets("Sheet2")
            .Cells(Dn.Row, 1).EntireRow.ClearContents
            .Range(nRng.Address) = Temp
        [COLOR=navy]End[/COLOR] With
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick