Need help in transposing the cells

[naveentr]

Hi all,

I have some 1000 lines of data in the below given format, and I am in very much need of your help in transposing that to the expected format..

Thanks all in advance, and please do the needful as this is bit urgent


Present data

36922 CHI-00-PROD
37338
36922 CHI-10-PROD
37338
37338 KAU1P045
37892
36685 KAU1P050
36727
36685 KAU1P051
36727
36685 KAU1P091
36922
36727
36685 KAU1P092
36922
37338
36727
36685 KAU1P116
36727
37338
36685 KAU1P117
36727
36568
36569
37338

I have shared the link of the file which has the expected data format. Please do the needful

http://www.mediafire.com/file/k6894tnifpny6jg/unique%20transpose.xls


Many thanks in advance

 

[hiker95]

naveentr,

Welcome to the MrExcel forum.

What cells are the actual first two pieces of raw data in?

36922 CHI-00-PROD

 

[naveentr]

Hi hiker95,

It starts from A1 and B1 and runs for around 1000 lines. I have mentioned the link to the spreadsheet which has raw data and the expected data.

Thanks for the timely reply

 

[hiker95]

naveentr,


Sample raw data before the macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	36922	CHI-00-PROD
2	37338
3	36922	CHI-10-PROD
4	37338
5	37338	KAU1P045
6	37892
7	36685	KAU1P050
8	36727
9	36685	KAU1P051
10	36727
11	36685	KAU1P091
12	36922
13	36727
14	36685	KAU1P092
15	36922
16	37338
17	36727
18	36685	KAU1P116
19	36727
20	37338
21	36685	KAU1P117
22	36727
23	36568
24	36569
25	37338
26
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	36922	CHI-00-PROD		36922	37338	37892	36685	36727	36568	36569
2	37338			CHI-00-PROD	CHI-00-PROD
3	36922	CHI-10-PROD		CHI-10-PROD	CHI-10-PROD
4	37338				KAU1P045	KAU1P045
5	37338	KAU1P045					KAU1P050	KAU1P050
6	37892						KAU1P051	KAU1P051
7	36685	KAU1P050		KAU1P091			KAU1P091	KAU1P091
8	36727			KAU1P092	KAU1P092		KAU1P092	KAU1P092
9	36685	KAU1P051			KAU1P116		KAU1P116	KAU1P116
10	36727				KAU1P117		KAU1P117	KAU1P117	KAU1P117	KAU1P117
11	36685	KAU1P091
12	36922
13	36727
14	36685	KAU1P092
15	36922
16	37338
17	36727
18	36685	KAU1P116
19	36727
20	37338
21	36685	KAU1P117
22	36727
23	36568
24	36569
25	37338
26
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, 08/02/2011
' http://www.mrexcel.com/forum/showthread.php?t=568649
Dim AB() As Variant, Area As Range
Dim LR As Long, a As Long, NR As Long, SR As Long, ER As Long, FC As Long, LC
Application.ScreenUpdating = False
LR = Cells(Rows.Count, 1).End(xlUp).Row
AB = Range("A1:B" & LR)
Rows(1).Insert
Cells(1, 1) = "A"
Columns(1).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns(4), Unique:=True
LR = Cells(Rows.Count, 4).End(xlUp).Row
Range("D1").Resize(, LR - 1) = Application.Transpose(Range("D2:D" & LR))
Range("D2:D" & LR).ClearContents
LR = Cells(Rows.Count, 1).End(xlUp).Row
For a = LR + 1 To 2 Step -1
  If Cells(a, 2) <> "" Then Rows(a).Insert
Next a
NR = 1
For Each Area In Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
  With Area
    SR = .Row
    ER = SR + .Rows.Count - 1
    NR = NR + 1
    For a = SR To ER Step 1
      FC = 0
      On Error Resume Next
      FC = Application.Match(Cells(a, 1), Rows(1), 0)
      On Error GoTo 0
      If FC <> 0 Then
        Cells(NR, FC) = Cells(SR, 2)
      End If
    Next a
  End With
Next Area
Columns("A:B").ClearContents
Range("A1:B" & UBound(AB)).Value = AB
LC = Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(1, 4), Cells(UBound(AB), LC)).Columns.AutoFit
Application.ScreenUpdating = True
End Sub

Then run the ReorgData macro.

 

[naveentr]

Hi Hiker95,

That was brilliant, and worked for me very well.. i heartily appreciate and thank you again.

Hiker, I need to have the output in same format but the input will be interchanged, I mean "Col A will be Col B" and "Col B will be Col A".

Thanks in advance.

I do have many more queries and will be disturbing you a lot again in coming days.

Thanks a lot again

 

[hiker95]

naveentr,

Please post another workbook with the raw data in columns A and B, and in the columns to the right what the re-organized data (manually formatted by you) should look like.

 

[naveentr]

Hi Hiker,

I am attaching the link to the file which has raw data and expected format. Even in this it is as same before, the raw data will run for 1000s of lines and the expected result is as same in the before said example.
You can assume that the raw data will be starting from A1 and B1.

Link:
http://www.mediafire.com/file/83uz68kyu1598zy/unique transpose2.xls

 

[hiker95]

naveentr,


New sample raw data:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	CHI-00-PROD	36922
2		37338
3	CHI-10-PROD	36922
4		37338
5	KAU1P045	37338
6		37892
7	KAU1P050	36685
8		36727
9	KAU1P051	36685
10		36727
11	KAU1P091	36685
12		36922
13		36727
14	KAU1P092	36685
15		36922
16		37338
17		36727
18	KAU1P116	36685
19		36727
20		37338
21	KAU1P117	36685
22		36727
23		36568
24		36569
25		37338
26
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	CHI-00-PROD	36922		36922	37338	37892	36685	36727	36568	36569
2		37338		CHI-00-PROD	CHI-00-PROD
3	CHI-10-PROD	36922		CHI-10-PROD	CHI-10-PROD
4		37338			KAU1P045	KAU1P045
5	KAU1P045	37338					KAU1P050	KAU1P050
6		37892					KAU1P051	KAU1P051
7	KAU1P050	36685		KAU1P091			KAU1P091	KAU1P091
8		36727		KAU1P092	KAU1P092		KAU1P092	KAU1P092
9	KAU1P051	36685			KAU1P116		KAU1P116	KAU1P116
10		36727			KAU1P117		KAU1P117	KAU1P117	KAU1P117	KAU1P117
11	KAU1P091	36685
12		36922
13		36727
14	KAU1P092	36685
15		36922
16		37338
17		36727
18	KAU1P116	36685
19		36727
20		37338
21	KAU1P117	36685
22		36727
23		36568
24		36569
25		37338
26
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 ReorgDataV2()
' hiker95, 08/02/2011
' http://www.mrexcel.com/forum/showthread.php?t=568649
Dim AB() As Variant, Area As Range
Dim LR As Long, a As Long, NR As Long, SR As Long, ER As Long, FC As Long, LC As Long
Application.ScreenUpdating = False
LR = Cells(Rows.Count, 2).End(xlUp).Row
AB = Range("A1:B" & LR)
Rows(1).Insert
Cells(1, 2) = "B"
Columns(2).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns(4), Unique:=True
LR = Cells(Rows.Count, 4).End(xlUp).Row
Range("D1").Resize(, LR - 1) = Application.Transpose(Range("D2:D" & LR))
Range("D2:D" & LR).ClearContents
LR = Cells(Rows.Count, 2).End(xlUp).Row
For a = LR + 1 To 2 Step -1
  If Cells(a, 1) <> "" Then Rows(a).Insert
Next a
NR = 1
For Each Area In Range("B2", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
  With Area
    SR = .Row
    ER = SR + .Rows.Count - 1
    NR = NR + 1
    For a = SR To ER Step 1
      FC = 0
      On Error Resume Next
      FC = Application.Match(Cells(a, 2), Rows(1), 0)
      On Error GoTo 0
      If FC <> 0 Then
        Cells(NR, FC) = Cells(SR, 1)
      End If
    Next a
  End With
Next Area
Columns("A:B").ClearContents
Range("A1:B" & UBound(AB)).Value = AB
LC = Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(1, 4), Cells(UBound(AB), LC)).Columns.AutoFit
Application.ScreenUpdating = True
End Sub

Then run the ReorgDataV2 macro.

 

[naveentr]

Hi Hiker,

Many thanks for your kind assistance.

I am posting a link to a spreadsheet, In sheet1 I have a lot of raw data, and there is a corresponding pivot table in the sheet2.

What I am in need of now is, in the pivot table i need to omit the results of server name which are present in only one RFC,

In other words I need the data of only the server names which is present in two or more RFC. Is there anyway of doing that in the pivot
table itself or you can guide me on that.

I have highlightes some server in yellow for your reference.

The output can be in the same format as pivot table looks.

And, really you will be saving my lot of work time if this can be achieved.

Thank you

Link:
http://www.mediafire.com/file/po178m3e648uyop/conflict.xls

 

[hiker95]

naveentr,

I will not download anymore workbooks from mediafire. This site launches/opens other web sites.

It would appear that in your latest workbook you are using the original ReorgData macro, but the raw data is represented per your latest request.

Last nite, becuase I am learning how to use arrays to manipulate raw data in memory, I was able to create a new macro that should be a lot faster then the first two macros.

But, I am now very confused.

Click on the Post Reply button, and just put the word BUMP in the post. Then, click on the Submit Reply button, and someone else will assist you.