How to arrange file names?

[rumshar]

Hi All,
I need a help. I have an excel file where in Col A File names are listed and in col B Full File Path & Names are listed. What I want to do is Col A should remain as it is but contents of Col B should be arranged in such a way that they get listed across.
I don't think I will be able to explain in words hence I have attached an excel file. May I request you to have a look @ the spreadsheet and give me a VBA solution? Thanks for your help.

With Regards
Rudra

 

[Smallman]

Rudra

Quick question - Why isn't Alabama Heart and Vascular Medicine in Row 5. Without more examples I can't really tell if you just made an error or the file is in the format it is meant to be in. Can you clarify?

Smallman

 

[rumshar]

Rudra

Quick question - Why isn't Alabama Heart and Vascular Medicine in Row 5. Without more examples I can't really tell if you just made an error or the file is in the format it is meant to be in. Can you clarify?

Smallman

Hi Friend,
Thank you very much for your reply. I have not made any error on listing files.
Col A files are master files(position shouldn't be changed). Files in Range B2:B4 are supporting files for Master File(A2) (these files should be listed across master file).Like wise Files in Range B5:B7 are supporting files for master file(A3).

 

[Smallman]

You need to give more than 2 examples. I can't tell if it is a 1 to 3 sequence or it is some other construction. How are you meant to see the pattern when there is only 2 examples? You have to think like the person viewing the file - will there be enough information in the file I am providing to derive a solution. Good question to ask before you post.

Smallman

 

[rumshar]

You need to give more than 2 examples. I can't tell if it is a 1 to 3 sequence or it is some other construction. How are you meant to see the pattern when there is only 2 examples? You have to think like the person viewing the file - will there be enough information in the file I am providing to derive a solution. Good question to ask before you post.

Smallman

Hi,
Here is my full client list
With Regards
Rudra

 

[Smallman]

Hi Rumshar

The following should solve your problem. Please remember to include enough information in future.

Option Explicit

Sub TransposeData()
Dim i As Integer
Dim ar As Variant

Application.ScreenUpdating = False
    ar = Range("A2", Range("A" & Rows.Count).End(xlUp))
    Sheet2.[a2:Z1000].ClearContents
   
    For i = 1 To UBound(ar)
        Sheet2.Range("A" & Rows.Count).End(xlUp)(2) = ar(i, 1)
        [b1:B1000].AutoFilter 1, "=*" & ar(i, 1) & "*"
        Range("b2", Range("b" & Rows.Count).End(xlUp)).Copy
        Sheet2.Range("B" & Rows.Count).End(xlUp)(2).PasteSpecial Transpose:=True
    Next i
    [b1].AutoFilter
    Application.ScreenUpdating = True
End Sub

File attached to show workings.

Take care

Smallman

 

[rumshar]

Hi Rumshar

The following should solve your problem. Please remember to include enough information in future.

Option Explicit

Sub TransposeData()
Dim i As Integer
Dim ar As Variant

Application.ScreenUpdating = False
    ar = Range("A2", Range("A" & Rows.Count).End(xlUp))
    Sheet2.[a2:Z1000].ClearContents
  
    For i = 1 To UBound(ar)
        Sheet2.Range("A" & Rows.Count).End(xlUp)(2) = ar(i, 1)
        [b1:B1000].AutoFilter 1, "=*" & ar(i, 1) & "*"
        Range("b2", Range("b" & Rows.Count).End(xlUp)).Copy
        Sheet2.Range("B" & Rows.Count).End(xlUp)(2).PasteSpecial Transpose:=True
    Next i
    [b1].AutoFilter
    Application.ScreenUpdating = True
End Sub

File attached to show workings.

Take care

Smallman

Hi Smallman,
This worked perfectly fine, thank you very much for your patience and help. You have saved my hours of time.
Do take care.
With Regards
Rudra

 

[Smallman]

No worries Rudra

Thanks for the kind words and we look forward to seeing you next time.

Smallman

 

[rumshar]

Hi Smallman,
I tried to understand this code and got to know that you have used Array in coding. Although I do little bit of VBA but I really don't understand how Array works. In the code you sent, I understood everything except one i.e
Sheet2.Range("A" & Rows.Count).End(xlUp)(2) = ar(i, 1).
Can you please explain what is this (2) in this line(Highlighted one)?

With Regards
Rudra

 

[Smallman]

Hi Rudra

This line;

Sheet2.Range("A" & Rows.Count).End(xlUp)(2) = ar(i, 1)

Is just saying take the data in the Array which we have just created and place the instance in question into the last used row (first blank row) of Column A of sheet 2.

Sheet2.Range("A" & Rows.Count).End(xlUp)(1)

you would get the last used row. If you put a 2 in the brackets you get the first blank row.

Some people write it like the following

Sheet2.Range("A" & Rows.Count).End(xlUp).offset(1, 0)

Which means the same thing. I prefer the method I have used for brevity.

Hope this helps my friend.

Take it easy

Smallman

 

[Smallman]

Rudra

The way I think about Arrays is think of a Spreadsheet Grid with data going across the columns and down the Rows.

Arrays work across the Columns and down the Rows, so the first element in an array is the data in Row 1

So if A1, A2, A3 contained
A, B, C

and we assigned ar = [a1:c1] then
ar(1)
ar(1,1) = A
ar(1,2) = B
ar(1,3) = C

the 1 in ar(1 stands for the Row number. So it is exactly the same as if you were to say

Cells(1,1)

which would be equal to A1.

Lets take this concept to the next row.

If we were to say

ar = [a1:c2]

now our array is 2 rows by 3 columns

ar(1)
ar(1,1) = A
ar(1,2) = B
ar(1,3) = C
ar(2)
ar(2,1) = D
ar(2,2) = E
ar(2,3) = F

See how the only thing which changes is the Row as we grow our Array. Only the Rows have increased and the Columns are staying the same.

The example I used in your coding was very simple as the data is in many Rows and 1 column. So I can trap a deep array which is narrow. I can then use a simple loop to iterate through the array and pass each of the variables in the array to a filter.

If that is not clear ask some more as you only learn by asking questions either of yourself or others.

Take care

Smallman

 

[rumshar]

Hi Rudra

This line;

Sheet2.Range("A" & Rows.Count).End(xlUp)(2) = ar(i, 1)

Is just saying take the data in the Array which we have just created and place the instance in question into the last used row (first blank row) of Column A of sheet 2.

Sheet2.Range("A" & Rows.Count).End(xlUp)(1)

you would get the last used row. If you put a 2 in the brackets you get the first blank row.

Some people write it like the following

Sheet2.Range("A" & Rows.Count).End(xlUp).offset(1, 0)

Which means the same thing. I prefer the method I have used for brevity.

Hope this helps my friend.

Take it easy

Smallman

Oh My God,
I never realized that it was offset function. Thank you very much for your explanation. Have a nice day!!!

 

[Smallman]

You are most welcome Rudra

See you next time.

Smallman