Newbie Question

Hello Everyone. I am a complete newbie to Macro's and would ask 2 things.

1.) Does anyone have some good links to Tutorials on Macros?

2.) I have an excel sheet that has a column of numbers and I would like to
have the sheet show me what row the numbers are in.

A small example:

ROW Col.A Col.B Col.C.
001 01 02 03
002 04 03 02
003 01 02 04
004 02 03 04
005 01 03 04
006 04 03 02

I would like a new sheet created or copied into a preset sheet so it would
look like:

Col.A Col.B Col.C Col.D Col.E Col.F
01 001 003 005
02 001 002 003 004 006
03 001 002 004 005 006
04 002 003 004 005 006

Thank you for any assistance.
David

Hi,
Your question raises a lot of other questions. The numbers listed all have
leading zeros, which is not normally the case in Excel, so I guess the first
question is whether these are Text or numbers? Then the next question might
be are the only "numbers" 1-4 or can there be an unlimited number of numbers?

Thanks,

"David" wrote:

> Hello Everyone. I am a complete newbie to Macro's and would ask 2 things.
>
> 1.) Does anyone have some good links to Tutorials on Macros?
>
> 2.) I have an excel sheet that has a column of numbers and I would like to
> have the sheet show me what row the numbers are in.
>
> A small example:
>
> ROW Col.A Col.B Col.C.
> 001 01 02 03
> 002 04 03 02
> 003 01 02 04
> 004 02 03 04
> 005 01 03 04
> 006 04 03 02
>
> I would like a new sheet created or copied into a preset sheet so it would
> look like:
>
> Col.A Col.B Col.C Col.D Col.E Col.F
> 01 001 003 005
> 02 001 002 003 004 006
> 03 001 002 004 005 006
> 04 002 003 004 005 006
>
> Thank you for any assistance.
> David

They are custom numbers that are formatted in the 00 format. I would say they
can be from number 1 - 99. I would not need it higher then that but I am sure
if it was able then going higher then 99 would be fine.

Thank you for any assistance in this.
David

"David" wrote:

> Hi,
> Your question raises a lot of other questions. The numbers listed all have
> leading zeros, which is not normally the case in Excel, so I guess the first
> question is whether these are Text or numbers? Then the next question might
> be are the only "numbers" 1-4 or can there be an unlimited number of numbers?
>
> Thanks,
>
> "David" wrote:
>
> > Hello Everyone. I am a complete newbie to Macro's and would ask 2 things.
> >
> > 1.) Does anyone have some good links to Tutorials on Macros?
> >
> > 2.) I have an excel sheet that has a column of numbers and I would like to
> > have the sheet show me what row the numbers are in.
> >
> > A small example:
> >
> > ROW Col.A Col.B Col.C.
> > 001 01 02 03
> > 002 04 03 02
> > 003 01 02 04
> > 004 02 03 04
> > 005 01 03 04
> > 006 04 03 02
> >
> > I would like a new sheet created or copied into a preset sheet so it would
> > look like:
> >
> > Col.A Col.B Col.C Col.D Col.E Col.F
> > 01 001 003 005
> > 02 001 002 003 004 006
> > 03 001 002 004 005 006
> > 04 002 003 004 005 006
> >
> > Thank you for any assistance.
> > David

Hi Again,
Will the numbers always be consecutive and with out gaps? What I mean is
that they will alwys be like 1,2,3,4 and not 1,3,4, no gaps?

Thanks,

"David" wrote:

> They are custom numbers that are formatted in the 00 format. I would say they
> can be from number 1 - 99. I would not need it higher then that but I am sure
> if it was able then going higher then 99 would be fine.
>
> Thank you for any assistance in this.
> David
>
> "David" wrote:
>
> > Hi,
> > Your question raises a lot of other questions. The numbers listed all have
> > leading zeros, which is not normally the case in Excel, so I guess the first
> > question is whether these are Text or numbers? Then the next question might
> > be are the only "numbers" 1-4 or can there be an unlimited number of numbers?
> >
> > Thanks,
> >
> > "David" wrote:
> >
> > > Hello Everyone. I am a complete newbie to Macro's and would ask 2 things.
> > >
> > > 1.) Does anyone have some good links to Tutorials on Macros?
> > >
> > > 2.) I have an excel sheet that has a column of numbers and I would like to
> > > have the sheet show me what row the numbers are in.
> > >
> > > A small example:
> > >
> > > ROW Col.A Col.B Col.C.
> > > 001 01 02 03
> > > 002 04 03 02
> > > 003 01 02 04
> > > 004 02 03 04
> > > 005 01 03 04
> > > 006 04 03 02
> > >
> > > I would like a new sheet created or copied into a preset sheet so it would
> > > look like:
> > >
> > > Col.A Col.B Col.C Col.D Col.E Col.F
> > > 01 001 003 005
> > > 02 001 002 003 004 006
> > > 03 001 002 004 005 006
> > > 04 002 003 004 005 006
> > >
> > > Thank you for any assistance.
> > > David

I can understand all the questions for clarification and I appreciate it. My
thoughts are that yes the numbers will be consecutive down the rows like:

1
2
3
4

The columns might have gaps going from 1, 3, 5, 12, 23, 45.. etc. like:
(where Col.B through Col.<whatever letter> would be what rows the number 01,
02, 03, or 04, etc. originally were located.)

Col.A Col.B Col.C Col.D Col.E Col.F
01 001 003 005
02 001 002 003 004 006
03 001 002 004 005 006
04 002 003 004 005 006

Thank you
David



"David" wrote:

> Hi Again,
> Will the numbers always be consecutive and with out gaps? What I mean is
> that they will alwys be like 1,2,3,4 and not 1,3,4, no gaps?
>
> Thanks,
>
> "David" wrote:
>
> > They are custom numbers that are formatted in the 00 format. I would say they
> > can be from number 1 - 99. I would not need it higher then that but I am sure
> > if it was able then going higher then 99 would be fine.
> >
> > Thank you for any assistance in this.
> > David
> >
> > "David" wrote:
> >
> > > Hi,
> > > Your question raises a lot of other questions. The numbers listed all have
> > > leading zeros, which is not normally the case in Excel, so I guess the first
> > > question is whether these are Text or numbers? Then the next question might
> > > be are the only "numbers" 1-4 or can there be an unlimited number of numbers?
> > >
> > > Thanks,
> > >
> > > "David" wrote:
> > >
> > > > Hello Everyone. I am a complete newbie to Macro's and would ask 2 things.
> > > >
> > > > 1.) Does anyone have some good links to Tutorials on Macros?
> > > >
> > > > 2.) I have an excel sheet that has a column of numbers and I would like to
> > > > have the sheet show me what row the numbers are in.
> > > >
> > > > A small example:
> > > >
> > > > ROW Col.A Col.B Col.C.
> > > > 001 01 02 03
> > > > 002 04 03 02
> > > > 003 01 02 04
> > > > 004 02 03 04
> > > > 005 01 03 04
> > > > 006 04 03 02
> > > >
> > > > I would like a new sheet created or copied into a preset sheet so it would
> > > > look like:
> > > >
> > > > Col.A Col.B Col.C Col.D Col.E Col.F
> > > > 01 001 003 005
> > > > 02 001 002 003 004 006
> > > > 03 001 002 004 005 006
> > > > 04 002 003 004 005 006
> > > >
> > > > Thank you for any assistance.
> > > > David

Hi,
Try this. It assumes numbers in range 1- 99 and collates the rows
for each number on Sheet2. If a number is not found in the original range,
the corresponding row in Sheet2 is blank.


Sub FindRows()

Dim rng As Range, rngA As Range, c As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Dim i As Integer, idx As Long

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

ws1.Select
' Change this range as required
Set rng = ws1.Range("A1:D" & Cells(Rows.Count, "A").End(xlUp).Row)

For i = 1 To 99 ' Set numbers 1 to 99 in col A of Sheet2
ws2.Cells(i, 1) = i
Next i

For Each c In rng ' Loop through each cell in selected range
idx = c.Value ' Row index in Sheet 2
With ws2
Set rngA = .Range("A" & Trim(Str(idx)) & ":IV" & Trim(Str(idx)))
' COUNTA is used to determine last used column for selected row IDX
.Cells(idx, Application.CountA(rngA) + 1) = c.Row ' Add to next column
End With
Next c

End Sub

HTH

"David" wrote:

> Hello Everyone. I am a complete newbie to Macro's and would ask 2 things.
>
> 1.) Does anyone have some good links to Tutorials on Macros?
>
> 2.) I have an excel sheet that has a column of numbers and I would like to
> have the sheet show me what row the numbers are in.
>
> A small example:
>
> ROW Col.A Col.B Col.C.
> 001 01 02 03
> 002 04 03 02
> 003 01 02 04
> 004 02 03 04
> 005 01 03 04
> 006 04 03 02
>
> I would like a new sheet created or copied into a preset sheet so it would
> look like:
>
> Col.A Col.B Col.C Col.D Col.E Col.F
> 01 001 003 005
> 02 001 002 003 004 006
> 03 001 002 004 005 006
> 04 002 003 004 005 006
>
> Thank you for any assistance.
> David

That was exactly what I needed. Did the trick perfectly.
Thank you.

David

"Toppers" wrote:

> Hi,
> Try this. It assumes numbers in range 1- 99 and collates the rows
> for each number on Sheet2. If a number is not found in the original range,
> the corresponding row in Sheet2 is blank.
>
>
> Sub FindRows()
>
> Dim rng As Range, rngA As Range, c As Range
> Dim ws1 As Worksheet, ws2 As Worksheet
> Dim i As Integer, idx As Long
>
> Set ws1 = Worksheets("Sheet1")
> Set ws2 = Worksheets("Sheet2")
>
> ws1.Select
> ' Change this range as required
> Set rng = ws1.Range("A1:D" & Cells(Rows.Count, "A").End(xlUp).Row)
>
> For i = 1 To 99 ' Set numbers 1 to 99 in col A of Sheet2
> ws2.Cells(i, 1) = i
> Next i
>
> For Each c In rng ' Loop through each cell in selected range
> idx = c.Value ' Row index in Sheet 2
> With ws2
> Set rngA = .Range("A" & Trim(Str(idx)) & ":IV" & Trim(Str(idx)))
> ' COUNTA is used to determine last used column for selected row IDX
> .Cells(idx, Application.CountA(rngA) + 1) = c.Row ' Add to next column
> End With
> Next c
>
> End Sub
>
> HTH
>
> "David" wrote:
>
> > Hello Everyone. I am a complete newbie to Macro's and would ask 2 things.
> >
> > 1.) Does anyone have some good links to Tutorials on Macros?
> >
> > 2.) I have an excel sheet that has a column of numbers and I would like to
> > have the sheet show me what row the numbers are in.
> >
> > A small example:
> >
> > ROW Col.A Col.B Col.C.
> > 001 01 02 03
> > 002 04 03 02
> > 003 01 02 04
> > 004 02 03 04
> > 005 01 03 04
> > 006 04 03 02
> >
> > I would like a new sheet created or copied into a preset sheet so it would
> > look like:
> >
> > Col.A Col.B Col.C Col.D Col.E Col.F
> > 01 001 003 005
> > 02 001 002 003 004 006
> > 03 001 002 004 005 006
> > 04 002 003 004 005 006
> >
> > Thank you for any assistance.
> > David