How to copy data from multiple column to single column

[harini123]

Hi,

I have an Excel worksheet, which is having some 100 rows of data. Each row / line has a same data but in different cell for example B4, C8, D15 & F20 all has same data say "John" like that. Now I want to copy all "John" to Column A2, A3, A4, etc. in a new worksheet. In the first worksheet Column A has fixed data. Each row has to be re-aligned so that consolidation of data will be easy.

Can expert’s thro some light on this?

Many thanks in advance.

 

[TonyJollans]

Hi harini123,

I'm afraid I don't understand your question. You say each row has the same data somewhere in some column and you want to copy it to a new sheet, so the new sheet will look like this (which can't really be what you want):

John
John
John
:
:

You also seem to give examples of the same data being in a varying row in each column rather than a varying column in each row.

What seems to be the basic question is how to realign cells and the answer really depends on what you want to do with the cells which need moving out of the way

If you have, say

A         B         C         D         E    
Fix       John      and       other     data
Fix       not       John      and       Jane
Fix       a         lot       before    John

Then this will bring all the Johns into line (a tall order, I suspect J) by deleting what comes between Column A and them.

Dim R As Long    
For R = 1 To 3
  Range(Cells(R, 2), Cells(R, Rows(R).Find(What:="John").Column)).Delete Shift:=xlToLeft
Next R

Giving a result of

A         B         C         D         E    
Fix       John      and       other     data
Fix       John      and       Jane
Fix       John

Please post a bit more info if this doesn’t help

Enjoy,
Tony

 

[Loomah]

Hi
Like Tony, I'm not really sure what you are trying to achieve. I'm working on the assumption that you want all your data listed according to what it is (though I can't see why!)

This will help create lists on a separate sheet but is heavy on user input. The result should be (using Tony's example data) something like:-
A B C D E
john jane and other data
john and
john

etc. depending on the input

Sub FindJohn()
Dim iCount As Integer
Dim lRow As Long
Dim strInp As String
Dim iCol As Integer

iCol = 1
'clear the destination sheet - REQUIRED????
Worksheets(1).Range("A1").CurrentRegion.ClearContents
Do
ReEnter:
    strInp = Trim(InputBox("Enter Name"))
    If strInp = "" Then Exit Do
        'source sheet
        With Worksheets(1)
            iCount = WorksheetFunction.CountIf(.Range("A1").CurrentRegion, strInp)
            If iCount = 0 Then
                MsgBox "Name not listed."
                GoTo ReEnter
            End If
        End With
        'destination sheet
        With Worksheets(2)
            .Select
            lRow = .Cells(65536, iCol).End(xlUp).Row
                If lRow <> 1 Then lRow = lRow + 1
            .Range(Cells(lRow, iCol), Cells(lRow + iCount - 1, iCol)) = strInp
        End With
    iCol = iCol + 1
Loop
End Sub

Good Luck!
;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[harini123]

Hi,

Sorry for the confusion. I will clarify here. I have an Excel worksheet like this:

A B C D E F G
1 1a 1b 1c 1d 1e -
2 1e - 1a 1f 1b 1d
3 1c 1b 1f - 1d 1e

From that I want a new worksheet like below:

A B C D E F G
1 1a 1b 1c 1d 1e -
2 1a 1b - 1d 1e 1f
3 - 1b 1c 1d 1e 1f

Total 2 3 2 3 3 2

Now, you got my problem. This is my requirement.

Thanks is advance

 

[TonyJollans]

Hi again,

There is probably an easier way but this should work. I am assuming that the first row has all the values you want in the order you want them.

On your second sheet ...

In cell A1 enter =Sheet1!A1
In cell B1 enter =IF(ISNA(MATCH(Sheet1!B$1,Sheet1!1:1,0)),"",Sheet1!B$1)

Copy cell B1 and Paste it into C1, D1, etc as far across as your data goes.
Now copy Row 1 and Paste it into rows 2, 3, etc as far down as your data goes.

Now in cell Bnnn (where nnn is the row below your data where you want your totals) enter =COUNTIF(B1:B3,Sheet1!B$1)

Copy cell Bnnn and Paste it into Cnnn, Dnnn, etc as far across as your data goes.

.. and Bob's your Uncle!

Enjoy,
Tony