Derek
Thankyou for your reply, David has resolved my problem.
Jim
>>> Derek Turner <g4swy@yahoo.com> 07/06/2012 18:18 >>>
Dear Jim
If I have understood this properly then each unique Nominal item can only have 9 instances (or less), corresponding to the 9 possibilities in column FR ?
So in your example 6001A would never occur in more than 9 rows ?
Is this correct ?
If so this may not be a problem to be solved with pivots, but pure VBA.
Regards
Derek +++
>________________________________
> From: Jim Fraser <james.fraser@port.ac.uk>
>To: ExcelVBA@yahoogroups.com
>Sent: Thursday, 7 June 2012, 9:12
>Subject: RE: [ExcelVBA] Pivot type problem
>
>
>
>David
>
>thankyou for your reply, yes the data does seem to have got gardled.
>
>The data should be in 3 columns: Noml which is 5 digits, four nos and a letter eg 6001A.
>
>the second column is called FR which has two digits and is numeric. The only items that can occur in column FR are the digits: 1, 2, 3, 10, 11, 20, 21, 22, 23.
>
>the third column which is called G which is one digit and can be alpha or numeric.
>
>I want to scroll down the Noml Column and select what is showing for that nominal against each incidence of FR. The individual nominals appear on several rows. So for instance nominal 6001A may appear on 4 rows so I would get an answer :
>FR| ! 1 ! 2 ! 3 ! 10 ! 11 !20 ! 21 !22 ! 23 !
>6001A ! A ! ! 8 ! A ! ! ! ! ! 0 !
>
>I want the data displayed with the individual nominals as rows and the FR items showing as column headings..
>
>The data presents itself :
>
>NOML!!FR!G!
>6001A!1!A!
>6002A!1!A!
>6001A!3!8!
>6002A!3!8!
>6073A!3!8!
>6074A!3!8!
>6075A!3!8!
>6001A!10!A!
>6002A!10!A!
>6001A!11!7!
>6003A!21!G!
>6001A!23!0!
>
>
>
>There are thousands of lines and each line has one nominal value, one FR value and one G value.
>
>Cheers.
>
>Jim
>
>
>
>
>
>>>> "David Grugeon" <yahoo@grugeon.com.au> 01/06/2012 14:33 >>>
>
>Hi Jim
>
>Seems you email got garbled on being translated to plain text. Could you
>try again an put, say, pipe characters between the columns, so we can
>understand the data. Then you need to make sure we can understand how the
>output is derived from the data. It looks as though the output is
>
>The first 5 characters of the data
>The seventh character of the data
>A space
>A numeral (where does this come from?)
>A character (Is this the seventh character again?)
>2 spaces
>Another numeral (Where does this come from?)
>
>Of course the required output and the way it is derived could be quite
>different. Without an explanation we cannot help you.
>
>What is an "Instance" of Noml? Is there one on each row? Or are there
>particular characters which have to match (like extracting all the amounts
>coded to a particular account code?)
>
>I hope you can help us to help you.
>It might help us to understand if you tell us what you are doing!
>
>Best Regards
>David Grugeon
>Excel VBA Group Moderator
>
>-----Original Message-----
>From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On Behalf
>Of Jim Fraser
>Sent: Friday, 1 June 2012 8:30 PM
>To: ExcelVBA@yahoogroups.com
>Subject: [ExcelVBA] Pivot type problem
>
>dear all
>
>I have 3 columns of data: NOML: FR: G. I wish to scroll down the NOML
>column and for each incidence of NOML record the incidence of the G column
>against the f reference. eg:
>
>Data
>
>NOMLFRG
>6001A1A
>6002A1A
>6001A38
>6002A38
>6073A38
>6074A38
>6075A38
>6001A10A
>6002A10A
>6001A117
>6003A21G
>6001A230
>
>
>Result:
>
>FR
>NOML123101120212223
>6001AA 8A 0
>
>There are thousands of rows and I want to select each incidence of noml. A
>pivot table will almost do it but it sums or avg etc the result, I just want
>to return whats in col g.
>
>Thanks in advance.
>
>Jim
>
>[Non-text portions of this message have been removed]
>
>------------------------------------
>
>----------------------------------
>Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
>here: http://www.mousetrax.com/techtrax to enter the ezine, then search the
>ARCHIVES for EXCEL VBA.
>
>----------------------------------
>Visit our ExcelVBA group home page for more info and support files:
>http://groups.yahoo.com/group/ExcelVBA
>
>----------------------------------
>More free tutorials and resources available at:
>http://www.mousetrax.com
>
>----------------------------------Yahoo! Groups Links
>
>
>
>[Non-text portions of this message have been removed]
>
>
>
>
>
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar