match data in differnet columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

See below example and advise how can I sort the dataset (excel spreadsheet) to show gaps as shown below.



x1 x1 to be x1 x

x2 x3 x

x3 x4 x3 x

x4 x4 x
 
Requires a completed list to the left of the list you want to sort. May have to change the cut and paste, if your "Complete" list is longer than 1000

Sub Macro1(
Do Until ActiveCell.Value = "
ActValue = ActiveCell.Valu
OrdValue = ActiveCell.Offset(0, -1).Valu
If ActValue = OrdValue The
ActiveCell.Offset(1, 0).Selec
Els
ActiveCell.Range("A1:A1000").Selec
Selection.Cu
ActiveCell.Offset(1, 0).Selec
ActiveSheet.Past
ActiveCell.Selec
End I
Loo
End Sub
 
One way which might work ..

Assume your data-set is in Sheet1, cols A and B
from row1 down

Also, col A is assumed to contain the "master" data list
i.e. the full data range of all the unique x's: x1, x2, x3, x4, etc

In a new Sheet2
----------------------

Put in A1: =Sheet1!A1

Put in B1:

=IF(ISNA(MATCH($A1,Sheet1!B:B,0)),"",OFFSET(Sheet1!$A$1,MATCH($A1,Sheet1!B:B
,0)-1,COLUMN()-1))

Select A1:B1
Copy down as many rows as there is data in Sheet1's col A

Col B will be "re-arranged" to the desired results

If there are other columns containing data
to be similarly re-arranged as was done for col B,
for example: in cols C, D, E, F, etc,
just copy the formula in col B across
(copy B1 across to say, F1, then copy down)

--
Rgds
Max
xl 97
---
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
----
Salah said:
See below example and advise how can I sort the dataset (excel
Click to expand...
spreadsheet) to show gaps as shown below.
 
Dear David

Thanks for the reply. However, it would be very nice if you could elaborate more on how to write this macro. I am a beginner in Excel and I need more details

Please bear with me

Thanks
 
Perhaps give my formula suggestion a try ?
It might suffice ..

--
Rgds
Max
xl 97
---
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
----
Salah said:
Dear David,

Thanks for the reply. However, it would be very nice if you could
Click to expand...
elaborate more on how to write this macro. I am a beginner in Excel and I
need more details.
 
Back
Top