Locate And Retrivie Data In Table/matrix
Sep 12, 2005
I have a dataset in one work sheet. The data, cells, can be lovated by three identifiers. Each column has an identifier (month) and each row have two identifier that has to combined. Region and Year.
Below is an example that shows the layout of the data.
1234
10120011.812.275.031.6
10120022.272.113.724.09
10120032.263.243.232.65
10120042.061.792.384.59
10120054.012.373.655.16
10220011.211.920.814.4
10220020.571.331.853.3
How can write a formula/macro where I can put the three identifiers and retrive the data/cell into a new work sheet?
View 14 Replies
ADVERTISEMENT
Jun 17, 2002
How to (by vba or whatever):
1) convert the Matrix data into the data table, and;
2) convert the data table into the matrix data
Matrix data (example)
share Ashare Bshare C
springsellholdbuy
summerbuybuysell
autumnsellholdhold
winterholdsellsell
data Table (example)
springshare Asell
summershare Abuy
autumnshare Asell
wintershare Ahold
springshare Bhold
summershare Bbuy
autumnshare Bhold
wintershare Bsell
springshare Cbuy
summershare Csell
autumnshare Chold
wintershare Csell
View 3 Replies
View Related
Jan 28, 2009
I am trying to convert the data I have to matrix format. I have attached a sample of the data in the file I have attached. The data is in the worksheet "Original" and the matrix format that I am trying to convert is in the "Transformed" worksheet. Can someone please help me with the VBA code for going from "Original" to "Transformed"?
View 11 Replies
View Related
Jun 19, 2014
I have a set of exported data from a Project Management SW (activecollab). The result is an excel .xslx file with a table inside with a lot of cells I am not going to use. Additionally, what I would like to do is creating new tables on other sheets that use only the data I want from the export. For example, I have two projects and three employees. I want to create three tables with the names of the three employees. In each table I want the tasks done by them and the time they will spend on them in order to create a Gantt chart. I need a solution that allows me to create new tables with selected data from a bigger cluster (the export). you do not need to give me the exact solution, I only need to know whether it is possible or not and where could I get the info to do what I want.
View 1 Replies
View Related
Jul 8, 2013
I have attached a sample data sheet which i am working on . ITs a comparitive matrix trable with Input validation between 0&2 . I also want the table to be dynamic .If i want to increase or reduce no. of rows and clumns i should do it some how . More details are mentioned in the attached file .
View 1 Replies
View Related
Oct 12, 2009
Struggling a bit on this one! I have tried the index function, but doesn’t quite work how I need it. Consider the attachment as an example:
I have three cells, 2 of which are drop down lists, and the third is simply the result from the input variables. The yellow and green section contains the info for my drop down lists, and the orange contains the data range. Trouble is ‘Index’ is looking at the contents of my cells, and not the corresponding cell address. So lets say I have cells F5 and A8, the answer I would be looking for is 23.7, but because the contents of my input cells are 185, and 5, its looking for column 185 and row 5, when I want it to look for the column/row that relates to these values, i.e. column 6, row 8.
View 5 Replies
View Related
Jun 4, 2014
I need to convert a Matrix to a Table. Something like this:
Input file:
Capture1.PNG
Output :
Capture2.PNG
View 4 Replies
View Related
Dec 26, 2013
I have a Table of multiple values. From this table, I need to lookup and match the corresponding value in another table, and return a "X" if the looked up values match.
For example: The table with the data in it is:
Apple
Bob
Candy
Bob
Cookie
Bob
Donut
Bob
Figs
Bob
The Table I need to create is: As follows: It will have Bob in the Top column, and the rows to the left of Bob will say, Apple, Asparagus, Candy, Cookie, Dancing, Donut, Fame, Figs, Zebra. I need to read the top table and populate with an "X" the values which have Bob.
View 9 Replies
View Related
May 25, 2006
I have a matrix that has been imported into excel from access. The column header is product number and the row header is name. I am then importing the data from an access query that puts the product/name matches in a small table to the right of the matrix (will be hidden when completed). I need to shade the corresponding cell in the matrix if that name has a match for that product. I tried =AND(MATCH(name details),MATCH(product details)) as my formatting equation, but that does not account for whether the matches are in the same row or not, so any combinations of products and names that appear in the whole table are shaded. I am not sure what other commands I can use to get the formatting equation to make sure that the matches are in the same row. Below is an example of my setup, "S" means it would be shaded.
------ 1----2----3----------------Name-------#
Name------------------------------Bob--------1
Bob---S----S----------------------Susan------1
Jane--------S----------------------Jane-------2
Susan-S---------S----------------Bob--------2
----------------------------------Susan------3
View 2 Replies
View Related
May 14, 2008
The "Sub Shortcut()" line is throwing the error.
Sub Shortcut()
Dim CellString As String
Dim BString As Boolean
Dim StrShort1 As String
StrShort1 = "Copy"
Sheets("Office - Generic").Activate
Range("A3:B14").Select
For Across = 1 To 12
View 5 Replies
View Related
Sep 11, 2006
Is there a easy way to find the last row of data in an Excel sheet using VB?
View 2 Replies
View Related
Aug 26, 2007
I have a data chart on 200 people with overlapping membership in 20+ groups, represeted as binary (1=member, 0=notmember), for example: ...
View 9 Replies
View Related
Aug 22, 2008
Using VBA, I wish to work out the inverse matrix of a large matrix (100*100), but keep getting the # Num! Error. I am using the minverse function. I have defined variable as "variant", does this give me the same possiblities in terms of number size as the variable "Double"?
View 9 Replies
View Related
Nov 30, 2006
I have this nice formula (listed below) that I'm trying to use to get the employee names that belong to each manager. How ever using the first formula I only get the first name in each cell and using the second formula (associates is a define name for the range A1:A70) gives me name that do not belong the that manager....
I would like this formula to only pull names from the manager that is listed in the C column....
View 9 Replies
View Related
Oct 4, 2006
I have 2 named ranges that are one besides the other - let's name them tTableA and tTableB. I also have a strValue, which holds a String I will be searching for in tTableA.
Now, tTableA contains names (strings), while tTableB contains quantities (numbers) for the corresponding names.
I need a Button that, when clicked, will do this:
1) Check to see if the strValue string is present in tTableA:
1.1 If NOT present, tell the user "Error".
1.2 If present continue
2) Now that we know strValue exists in tTableA, find the corresponding quantity located in tTableB (this quantity would be on the same 'row' as the row in tTableA which contains strValue)
2.1 If quantity <= 0 then tell the user "Nothing left"
2.2 else REDUCE that quantity by 1 unit.
View 8 Replies
View Related
Jul 11, 2006
I needed to find "406" which is in A5280 copy the previous 160 rows X 3 columns to A5281.
Because of other factors involved I now realise it would be better to have the macro do the following.
1. Find "04/06" (in A5123)
2. Copy from 2 rows above this cell (A5121) down to (C5280)
3. Paste into A5281
Sub ACopy3()
Const intRowsToCopy As Integer = 160
Dim rngFound As Range
Dim Ro
Dim Col
Application. ScreenUpdating = False
Set rngFound = Columns("A:A").Find(What:="406", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
Range(rngFound.Offset((intRowsToCopy - 1) * (-1), 2), rngFound).Copy rngFound.Offset(1, 0)
Application.ScreenUpdating = True
End Sub
View 9 Replies
View Related
Jul 4, 2014
I added an attachment with an example. You will see a matrix, with in the most left column an ID, then the headers of the matrix has different numbers that mean something.
What I want to do is, you fill in a number in the combobox, then press on a button and a report will be made on a new worksheet. With a list of the numbers and dates in the matrix that the ID has. Like shown in the example sheet in the workbook.
View 1 Replies
View Related
Sep 6, 2012
Here's a sample of the document I have, the original has several thousand entries, so figured for the sample I'd limit it. The raw data is on sheet 1, and sheet 2 is what I want to have to more easily manipulate the data. I started doing it manually, but I'm sure there's a far better method that I just don't know about yet. sample.xlsx
For those that don't want to download the document:
My data is like this:
ID1 Field1
ID1 Field2
ID1 Field3
ID2 Field1
ID2 Field2
ID2 Field3
ID3 Field1
etc.
What I want is this:
ID1 ID2
Field1 Field1
Field2 Field2
Field3 Field3
etc.
View 2 Replies
View Related
Apr 3, 2009
I am using the below code to locate the last column in various worksheets before pasting information. The problem is the column selected is either: Correct, Is a blank column with many blank columns in between it and the last visible text. The code also highlights all the cells containing text in some sheets. The results are the same for each sheet the code is run in i.e it is not varying.
View 4 Replies
View Related
Feb 8, 2012
I am in the process of comparing the data between 2 different sheets and the data on each sheet contains 2 columns of information. I am trying to find a way to compare one sheet to that same item on the second sheet and see if anything is missing, however the second sheet contains more information between the two columns and the rows are never the same during my comparison. I've tried Match, Lookup, Indirect, and if/then with an And functions and because the rows do not stay the same the data is not accurate. Here is a brief example of what I am working on:
SHEET 1
A B
1 Red Delicious Apple 125 Count Apple
2 Red Delicious Apple 125 Count Fresh Fruit
[Code]....
So in these comparisons, the names in Column A match identically, so if I try to say take the name from sheet 1 and match to that in sheet 2 and if the wording in Column B matches then put a "Yes" value. But you can see on Sheet 2 by the time it gets to rows 14, 15, 16, the Baby Food Chicken has a 3rd item in Column B in comaprison to Sheet 1, so any type of match doesn't work.
I am using Excel 2003.
View 9 Replies
View Related
Jun 18, 2012
Is there a way to do this?
a) run a code that will create a pop-up window with the question, "Can you show me where the CODEMAP.txt" file is?"
b) then the user will locate the file in a pop-up window and be able to click/select the file and there are "ok" and "cancel" buttons.
c) once the "codemap.txt" file is selected, the vba code will automatically change this file to "codemap.csv" so that it will open in excel (temporarily). This file can be placed anyway, even on the desktop because it won't be needed later. This is only temporary.
d) Then on the "codemap.csv" file, everything from sheet 1, cell b1 to cell b700 are copied and pasted to sheet two of this macro
The codemap.csv file can then be deleted
View 2 Replies
View Related
Jun 22, 2008
I'm running a small messenger company and I've got a geographic area divided into five zones: 1,2,3E,3W,& 4
Deliveries within 1 zone will be charged for one zone.
Deliveries from Zone 1 to Zone 2 will be charged for 2 zones. Etc.
I've got a list of zones in my From column and also in my To column.
I need to write a formula that will give my customers the Zones Charged in a seperate cell.
I learned how to do something like this back in school, but don't even know what to call it anymore. Maybe it's some kind of comples IF function.
FREE Deliveries to anyone in Long Beach, CA to the winner!!!
View 12 Replies
View Related
Feb 17, 2009
On my example sheet it shows how many cables and how the cable length per job. What i need is for the hour/job column to be populated from the Hours sheet by matching the column and row. e.g 4 cables at 40 metres would give me cell D7 (27.89), as 25 metres would be no good - so rounding up would be required.
View 2 Replies
View Related
Oct 18, 2011
I have 2 lines with pairs and 3rd with corresponding values, nee to construct a matrix out of it with formulas
Example
Need to turn
AAABBDBCDCDC0.30.20.130.550.840.43
into
ABCDA11-0.31-0.21-0.13B0.311-0.551-0.84C0.20.5510.43D0.130.841-0.431
So the one that stands above in the initial file goes to the top line in the matrix. 2 line -> column in matrix. In case there is no match need to seek reverse in 1,2 and reflect as 1-VALUE in the matrix
View 3 Replies
View Related
Aug 17, 2008
I have an Excel file with a sheet that has the following columns: Name, Activity, Start Date,
End Date. What I would like to do: I would like to create summary sheet with a calendar-like
matrix, with the names as rows, and dates as columns. I would then like to plot the activity in
the corresponding dates (based on the start and end date). Note: If there are schedule
conflicts, the conflict should show up in another row
- Is this even possible to do in Excel?
Source:
Name Activity StartDate EndDate
PersonA Vacation Jan1 Jan3
PersonA Office Jan4 Jan5
PersonA Field Jan6 Jan7
PersonB Vacation Jan1 Jan3
PersonB Field Jan4 Jan7
PersonB Office Jan5 Jan5
Summary:
Name Jan1 Jan2 Jan3 Jan4 Jan5 Jan6 Jan7
PersonA Vacation Vacation Vacation Office Office Field Field
PersonB Vacation Vacation Vacation Field Field Field Field
PersonB Office
View 9 Replies
View Related
Jun 12, 2014
have a formula or something along those lines that will look for the data you type in to the selected cell and will show you on how many workbooks it has been entered in. For example, I have workbook a, b, c, d, e, f. all have 12 sheets. On these 12 sheets there is a place to enter the serial number of an item. What I would like to be able to do is on a different workbook be able to type in a serial number and have it show which workbooks it has ever been typed in and its location on said workbooks. and if possible the results of the item which would be whether it was accepted or rejected( this bit is not totally must have. but the workbook and sheet location is a must.)
View 1 Replies
View Related
Mar 21, 2014
For a table like the one below produced for the sake of example (actual is much much bigger) I want to make it list rows that are true for a certain column for a certain variable in the matrix. So for say water terrain, which types of activity can I do i.e. swimming. Or for Offroad the activites which I can't do i.e. Run and Swim.
ActivityWaterRoadOffroad
Jog nym
Run nyn
Walk nyy
Swim ynn
y=yes
n=no
m=maybe
View 10 Replies
View Related
Jun 22, 2013
In VBA I'm trying to grab numbers from a cell range (ie: A1:A10), and save them to a matrix or variable. I will later need to grab additional numbers and add it to that matrix. I have experimented several different ways, but belows demonstrates what I'm trying to do:
VB:
my_data = Range("A1:A10").Value
my_data = my_data&Range("B1:B10").Value
View 2 Replies
View Related
Jan 30, 2013
I have 2 types of data: Product & Store
And the data would go like this:
A
Corn Flakes
Frosted Flakes
Froot Loops
B
Store 1
Store 2
Store 3
Store 4
The number of entries of course is a lot bigger, that's why I'm trying to make it auto.
So what I need is to have a table created that would mix both data type in 2 columns, like this:
Product
Store
Corn Flakes
Store 1
[Code]...
How would this be done automatically by a formula?
View 9 Replies
View Related
Nov 21, 2006
Im currently working to speed up my monthly reporting process. Each month I pull data from the ledger by account & office. Below is an example of what the format of the ledger looks like.
My next step is to report the amounts by account number for each office. At the moment I do this manually, which takes a very long time. My goal is to be able to paste the ledger data on a worksheet and have my report populate automatically.
In regards to the account numbers from the ledger, we summarize the accounts using the first 3 digits of the account. For example, account number "1113454" would be considered a "111" account. Account number 3335454 would be considered a "333" account.
************************************************************************>Microsoft Excel - 3Q Global Variance Analysis.xls___Running: xl2000 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB16C16D16E16F16B22E22B23E23B24E24B25E25B26E26=
ABCDEF1Ledger2******3*Office*1Office*2Office*3Office*4Office*54*SEP-06SEP-06SEP-06SEP-06SEP-065Account*Number*****6111234127.00766.004,446.007,865.005,509.00711135433,000.0034,566.0099,433.00100.00471,612.76811167645,677.009,898.002,212.0034.00877.00911190932,445.00466.0043,435.00343.005,665.00101118881,876.00433.006,566.00900.0020,398.4411113454121.0032.00900.0075,329.3222.001211345517,678.005,443.00434.00233.0064,556.00133331115,658.00875.00222.0032.00654.001433350090,932.007,634.00122,246.002,134.0055.0015333009123.00543.00990.009,005.00888.0016Sum227,637.0060,656.00280,884.0095,975.32570,237.2017******18My*Report*19******20111*Balance*113*Balance*21OfficeBalance*OfficeBalance*22Office*1113,125.00*Office*117,799.00*23Office*246,129.00*Office*25,475.00*24Office*3156,092.00*Office*31,334.00*25Office*49,242.00*Office*475,562.32*26Office*5504,062.20*Office*564,578.00*27******Ledger*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View 9 Replies
View Related
