Hi have basically have a very large spreadsheet that looks something like this:
Number Type Data
A b c
B s x
C r c
D f x
and i wanted to make it with the number for the rows, the type for the columns, and the data populating the spreadsheet. I could sort of get it to work using a pivot table but it would not show the actual data in the sheet just sum or count or whatever. Is it possible to do this?
Thanks
Last edited by Xman304; 06-21-2012 at 08:00 AM.
So for a given "Number" and a given "Type" would there be more than 1 match?
Down column A, you'd have a unique set of numbers and across row 1, you'd have a unique set of types?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Do you mean something like this?
not really, there will be multiple entries down column A that are the same, with a unique set of numbers across row one, then the data that populates the rest of the sheet will be in cells that match up accordingly. Hope that makes sense.
Then maybe the way ChemistB suggested.
Otherwise
You get better help if you post an Excel-example of your workbook, without confidential information.
Basically I have a huge sheet of Well Information. in column 1 I have the well number, in column 2 I have the formation, and column three I have codes entered by a user. Column one will contain like 5 entries for the same well number, while column two will contain differing information for that well eg formationA, formationB, then a code in the third column. Kinda Like this
Well A FormX Code1
Well A FormY Code2
Well A FormZ Code3
Well B FormX Code4
Well B FormZ Code5
And I want something like this
WELL FormX FormY FormZ
Well A Code1
Well A Code2
Well A Code3
Well B Code4
Well B Code5
You get better help if you post an Excel-example of your workbook, without confidential information.
Sample Book1.xlsx
Hope that worked
Different lay out.
With pivot table.
I think it will be possible with VBA.
Especialy now your mentioned you get a very large spreadsheet.
But i can't help you enoug with VBA.
Thanks Oeklere, I can get basically what I am looking for using a pivot table, but I want it to show the actual data in the sheet instead of a count, or a sum etc. I have tried a work around I found using Access, but the sheet is to large to copy out of Access.
Maybe.See my attachment.
I used an Array formula (entered with CNTRL SHFT ENTER instead of just ENTER which, if done right will show brackets {} around the formula)
=IFERROR(INDEX($C$2:$C$6,MATCH($A13&B$11,$A$2:$A$6&$B$2:$B$6,0)),"")
This does not separate them into different rows though (see attachment). If you really need it like that, (rather than what I put into A20:D23) maybe someone can modify.
That will work, now I just have to figure out how to get that formula modified too fit my sheet. I have 192595 rows, and columns thru EH when I make the pivot table.
Copy column A somewhere else and then select it in the new location and Data Tab>Remove Duplicates.
Do the same with column B, then copy and paste special > Transpose to get your unique types across the top.
Then figure out how to get that formula to work with those ranges.
Thanks, I will give it a shot!
Thanks so much it worked, I had to remove a $ one place you had it, and add another in another location to get it to work the way I needed it to.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks