David
Thank-you. Excellent.
Jim
>>> "David Grugeon" <yahoo@grugeon.com.au> 6/8/2012 5:07 AM >>>
I construct these manually in a minute or so
Select the whole of col A, copy
Open a new sheet. Click A5 and paste values
While the area is selected go to advanced filter and filter unique values. It will give you the option to put the list it in a new location. Click B5.
Delete col A
In Row 4 starting form Col C put The FR Values
On the original sheet insert a column after col B with the formula in C2 =A2&B2. Copy this down to the end of the list
On the target sheet in cell B6 put the formula
=iferror(vlookup($A6&B$5,Sheet1!$C:$D,2,false),"")
Copy this formula to the area B6 to the last row and column.
(End-Home will take you too far unless you close the sheet and reopen it after deleting column A.)
It seems to take a long time to explain but it is really only a minute to do.
Best Regards
David Grugeon
Excel VBA Group Moderator
-----Original Message-----
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On Behalf Of Jim Fraser
Sent: Thursday, 7 June 2012 6:13 PM
To: ExcelVBA@yahoogroups.com
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]
------------------------------------
----------------------------------
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]
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