if, lookup, transpose? not sure...

[MicJag]

hi there, can someone please help me again.

i have 4 of the same equipment's in a1,2,3,4 but b1,2,3,4 each has a different movement type. is there a way i can get the movement type in c1, d1, e1 & f1 and at the end be able to remove duplicates in column A.

i have 700 000 rows to do this for

help is much appreciated

michael

 

[hiker95]

MicJag,

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

 

[MicJag]

oh sorry, its office 2010,


10000000 231
10000000 232
10000000 922
10000001 921
10000001 922
10000002 921
10000003 921
10000003 922
10000004 921
10000004 922
10000005 921
10000005 922
10000006 921
10000006 922
10000007 921
10000007 922

sorry im a bit slow here, thought the example explanation was good enough

 

[hiker95]

MicJag,

I would prefer not to have to separate your data into separate columns, and/or remove any space characters.

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net, and provide us with a link to your workbook.

 

[MicJag]

something like this? will this help?

Excel Workbook
	A	B
1	Equipment	MvT1
2	10000000	231
3	10000000	232
4	10000000	922
5	10000001	921
6	10000001	922
7	10000002	921
8	10000003	921
9	10000003	922
10	10000004	921
11	10000004	922
12	10000005	921
13	10000005	922
14	10000006	921
15	10000006	922
Sheet1

Excel Workbook
	D	E	F	G	H	I
1	Equipment	MvT1	MvT2	MvT3	MvT4	MvT5
2	10000000	231	232	922	*	*
3	10000001	921	922	*	*	*
4	10000002	921	*	*	*	*
5	10000003	921	922	*	*	*
6	10000004	921	922	*	*	*
7	10000005	921	922	*	*	*
8	10000006	921	922	*	*	*
Sheet1

 

[hiker95]

MicJag,


Sample raw data in worksheet Sheet1 before the macro:

Excel Workbook
	A	B	C	D	E	F	G
1	Equipment	MvT1
2	10000000	231
3	10000000	232
4	10000000	922
5	10000001	921
6	10000001	922
7	10000002	921
8	10000003	921
9	10000003	922
10	10000004	921
11	10000004	922
12	10000005	921
13	10000005	922
14	10000006	921
15	10000006	922
16	10000007	921
17	10000007	922
18
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E	F	G
1	Equipment	MvT1		Equipment	MvT1	MvT2	MvT3
2	10000000	231		10000000	231	232	922
3	10000000	232		10000001	921	922
4	10000000	922		10000002	921
5	10000001	921		10000003	921	922
6	10000001	922		10000004	921	922
7	10000002	921		10000005	921	922
8	10000003	921		10000006	921	922
9	10000003	922		10000007	921	922
10	10000004	921
11	10000004	922
12	10000005	921
13	10000005	922
14	10000006	921
15	10000006	922
16	10000007	921
17	10000007	922
18
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
' hiker95, 07/22/2011
' http://www.mrexcel.com/forum/showthread.php?t=566243
Dim LR As Long, a As Long, SR As Long, ER As Long, LC As Long
Application.ScreenUpdating = False
LR = Cells(Rows.Count, 1).End(xlUp).Row
Range("A2:B" & LR).Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
  , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
  False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
Columns(1).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns(4), Unique:=True
LR = Cells(Rows.Count, 4).End(xlUp).Row
For a = 2 To LR Step 1
  SR = Application.Match(Cells(a, 4), Columns(1), 0)
  ER = Application.Match(Cells(a, 4), Columns(1), 1)
  If SR = ER Then
    Cells(a, 5).Value = Cells(SR, 2).Value
  Else
    Cells(a, 5).Resize(, ER - SR + 1).Value = Application.Transpose(Range("B" & SR & ":B" & ER).Value)
  End If
Next a
LC = Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
With Range(Cells(1, 5), Cells(1, LC))
  .Formula = "=""MvT"" & COLUMN()-4"
  .Value = .Value
End With
Range(Cells(1, 4), Cells(LR, LC)).Columns.AutoFit
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the ReorgData macro.

 

[MicJag]

sorry for all the trouble, i should learn how to get the questions out in the propper format here.

thank you very much for your help. thats why i love this forum

 

[snoopyhr]

Here is a none macro solution. adjust the range as needed

Excel Workbook
	A	B	C	D	E	F	G	H
1	Equipment	MVT1		SortedUnique List of Eqpmt.	No. Occourances
2	10000000	231		10000000	3	231	232	922
3	10000000	232		10000001	2	921	922
4	10000000	922		10000002	1	921
5	10000001	921		10000003	2	921	922
6	10000001	922		10000004	2	921	922
7	10000002	921		10000005	2	921	922
8	10000003	921		10000006	2	921	922
9	10000003	922		10000007	2	921	922
10	10000004	921
11	10000004	922
12	10000005	921
13	10000005	922
14	10000006	921
15	10000006	922
16	10000007	921
17	10000007	922
Sheet2

 

[hiker95]

MicJag,


Same screenshots as my Reply #6.


The following should run a lot faster, especially since you have 700,000 plus rows.

I also assume that there are no blank rows in your data set. If there are, let me know and I will adjust the macro.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgDataV2()
' hiker95, 07/22/2011
' http://www.mrexcel.com/forum/showthread.php?t=566320
' This macro code has been modified from the original code
'   by: mirabeau, 07/08/2011
'   http://www.mrexcel.com/forum/showthread.php?t=563070
Dim a, na As Long, m As Long, k As Long
Dim c(), p As Long, i As Long
i = Cells(Rows.Count, 1).End(xlUp).Row
Range("A2:B" & i).Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
  , Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
  Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
a = Range("A1").CurrentRegion.Resize(, 2)
na = UBound(a, 1)
m = 2
k = 1
ReDim c(1 To na, 1 To m)
For i = 2 To na
  If a(i, 1) <> a(i - 1, 1) Then
    k = k + 1
    p = 2
    c(k, 1) = a(i, 1)
    c(k, 2) = a(i, 2)
   Else
    p = p + 1
    If p > m Then
      m = p
      ReDim Preserve c(1 To na, 1 To m)
    End If
  c(k, p) = a(i, 2)
  End If
Next i
c(1, 1) = "Equipment"
For i = 2 To m
  c(1, i) = "MvT" & i - 1
Next i
With Range("D1").Resize(k, m)
  .Value = c
  .Columns.AutoFit
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the ReorgDataV2 macro.

 

[MicJag]

Thank you once again hiker, this one works much faster, 1000 times faster than the first one. i don't have blank rows in the data,

much appreciated

i just added a VBA coarse to my annual KPI's.

regards
Mic