Let's assume, starting at A1, that i have table (8c x 30r)
and lets assume that in each of the columns i have 5 cells filled in with data at random.
What is the best way for me to produce a list of the data in each of the columns without having to go in and delete out all the blanks myself? I.e. i want to reproduce the table but without any of the cells which do not have any data in.
I want to ask if there is a way to simply extract non-empty rows from a data set consisting of many rows filled with zeroes and some which are filled with the non-zero values. By “simply” I mean without using any macros. Any help or suggestions are highly appreciated.
Dima
I have attached the workbook with example of the sheet with such data set (CLEAN tab shows the desired result of this operation):
[url]
We have a large table on one worksheet that has specific information that we want on a second worksheet.
We can use VLOOKUP to get one line of information transferred over to the new worksheet, but how to bring many lines of data over.
In the attached example, we want ONLY the RE or RO or RL lines of data in worksheet "ALL CREDIT CARDS" to be listed on the new worksheet "RE RO RL CREDIT CARDS".
I have multiple rows in my files that have duplicate information save for one line of information as such.
View 12 Replies View RelatedBased on the 1st example below, I have a file with 1000's of columns where every 3 columns has the same type of info....
View 9 Replies View RelatedIn sort of a follow up to the Condensing A List Thread
[url]
I would like to condense the list into a series of list for each category.
The example below is a synoptic view with some data and does not include the full table.
I would like to do it with functions, i can do it with vb, at this point, I'm more on a mission to do it without it.
Windows 8, Excel 2010,
What is a condensed way of saying;
Dim x As Integer
Dim y As Integer
Dim z1 As Integer
Dim z2 As Integer
Dim z3 As Integer
Dim z4 As Integer
Dim AA As Integer
I have a pretty slick macro that once ran will save the spreadsheet and copy the input to a master sheet, so far it is very functional. I borrowed most of the code from within this forum, as VBA is not my strong point. My problem being that when the script runs and posts the output to the master sheet, it is pasting formulas. I need it to strictly paste the values in, otherwise my master sheet is referencing hundreds of other worksheets.
Sub Finished()
Dim iReply As Integer
Dim countColumn As Integer
Dim MyFullName As String
MyFullName = ThisWorkbook. Name
iReply = MsgBox(Prompt:="This will upload your tracker and exit, Are you sure?", _
Buttons:=vbYesNo, Title:="")
If iReply = vbYes Then
'If Yes Do This
Application.DisplayAlerts = False...................
I have some VBA that dumps various sheets data into an SQL Database.
Part of that requires me to sanitize all of the fields before they make it to the DB, (at least to prevent the code from breaking itself w/ errant ' characters.
right now my code is as follows
Code:
If InStr(aa, "'") > 0 Then
aa = Replace(aa, "'", "''")
End If
If InStr(bb, "'") > 0 Then
bb = Replace(bb, "'", "''")
[Code] ......
I was hoping to condense it to something like the following, however it is not working how I hoped / want it to. .. I had found somewhere out there that this Eval() function possibly could be used to 'reference' dynamic variables, however it does not appear to work at all anymore, and even then it may only have worked to 'read' and not 'write' to the variable. (Excel 2010, on Windows 7 64-bit).
Code:
itemsToSanitize = "aa,bb,cc,dd,ee,ff,gg,hh,ii,jj,kk,ll,mm,nn,oo,pp,qq,rr,ss,tt,uu,vv,ww,xx,yy,zz,aaa,bbb,ccc,ddd,eee,fff,ggg,hhh"
ITSArray = Split(itemsToSanitize, ",")
For Each thing In ITSArray
If InStr(Eval(thing), "'") > 0 Then
Eval(thing) = Replace(Eval(thing), "'", "''")
End If
Next thing
Using Excel 2010. I'm writing a macro that sets up a workbook to be used for estimating at the beginning of a project. In the code I need to create multiple tables (formerly known as "lists") in the workbook. Then later in the code I need to refer back to those newly created tables. Currently, the code that creates the table is part of a loop that creates the table on many different worksheets. The problem of course, is that I have to name the Table, and then it won't create a table of the same name on the next sheet. Then, later in the code, I need to make adjustments to the table that was just created before looping to the next sheet.
Is there a way to create a table without giving it a constant name? Or by giving it a name that builds off of other info in the sheet? For example, I would be good with the naming the table after the sheet name: "Sheet1_Table" or such.
Code:
Sub Auto_Open()
'
Dim sht As Worksheet
If Range("A1") = 1 Then
[Code].....
I want to use a Pivot table to filter data to show just the studies that contain patients from the 'South' area?
As per example below I want to be able to see all the patients in all areas but only for studies that have patients from the south. I put together an array formula that works well for small tables but is too much with one one my sheets that contains 200,000 rows.
Before filtering:
Study ID
Study Short Title
Study Patient ID
Area
1346
LLP
90126
Northwest
[code]....
I have been trying to find a way to consolidate data in two tables, but can't seem to locate a good response. I have been toying with EXACT, VLOOKUP, HLOOKUP, MATCH, and SUMPRODUCT, but none seem to work for my issue.
Two Files - Call them acrendo.xlsx and office.xlsx
Both have a single sheet and contain first name, last name, and date columns. Office also has other information that I'm trying to merge into the corresponding row of acrendo.
Basically, I am trying to take the combined first name, last name, and date column from a row in acrendo as a single record, look for an exact match in office, and if it finds one, return the corresponding values in Coumns D, E, F back to acrendo. Someone suggested using the following sumproduct formula:
=SUMPRODUCT(--(A2=[ofc.ally.xlsx]ofc.ally!$A$2:$A$9329),--(B2=[ofc.ally.xlsx]ofc.ally!$B$2:$B$9329),--(C2=[ofc.ally.xlsx]ofc.ally!$C$2:$C$9329),E:E)
This always returns a value of 0, even if there is data in the matching row to be copied over.
One small complicating matter is that there may be duplicate entries in office (i.e. one row in acrendo may match to four rows in office). I can cleanse that if necessary, or preferably, if multiple matches are found, it would simply insert the additional rows into the acrendo file.
Also, is there a way to go through the acrendo file and check for duplicate records and delete them (e.g. if there is the same last name, first name, and date, delete the duplicates).
Can't do this manually as I have acrendo has almost 18,000 rows and office has almost 10,000 rows.
I am trying to merge data from 2 tables into a master sheet. Column goes like this 1 = customer ID, 2 = allocated number, 3 = age
The first table has around 2000 entries and 2nd table has about 1000, and they both contain some overlapping entries. I wonder if there is a simple way to go around this rather than manually looking at each individual entries as I will be more tables to fit in later on.
I have recently discovered the "data table" tool on Excel and have found it to be extremely useful for sensitivity analysis. The Excel help describes how one can create 1 and 2 variable data tables. Now I would like to create a data table using three variables, however. (I would like to vary three different variables and see what effect they have on one formula.) Is this possible with Excel? Can pivot tables be used this way?
View 2 Replies View RelatedI need to check data from two tables(A and B). They are supposed to match each other and my job is to check if there's error in it. Each row contains several criteria, and all criteria need to be matched. I need to give a report on the info that
1) contains in table A but not in B
2) contains in table B but not in A
3) contains in A and B but some of these criteria do not match..
I have basic knowledge of VBA. Forget to say, they are in different order. And even after sorted, the name may still unmatch with each other, so can not use formular like "=Sheet1!A1=Sheet2!A1"
Example:..........
I have a pivot table i created to pull in data in a tabular format so i can have all of my data fileds listed in a columns. On another page I am doing a dashboard that is using Countifs to pull in data to my report. What I am running into is the countifs are only working when i filter my pivot table to a certain value that matches, otherwise it will not pull the counts in..
View 5 Replies View RelatedI have to use data tables to calculate Net Present Value (NPV) by altering specific variables.
How to use the data tables function under the what-if analysis.
My sheet and calculations are all set up
But for the life of me i cant figure out how im going to tell excel that im changing for example wage costs and not fixed assets when trying to asses the NPV.
Ive done it manually by altering the figures on the Inputs and Data Sheet.
My NPV calculation is on the Workings and calculations sheet.
The data tables i have done manually are on the Inputs and Data sheet.
I've encountered is the spreadsheet attached. Basically I am trying to create a "complex" array (at least it's complex for me lol) to basically extract some values from tables and put them in one together. The array I thought would work is on the table with BRIGHT YELLOW BACKGROUND, whereas the table that could do the same work but more "manually" is with a RED border around it. I know I can do it manually as the red border table shows, but I've been trying all day to make it work with a complex array.
View 4 Replies View RelatedI need to find the most frequent number for each name in a large datatable, eg. Name1=12.
ColumnA / ColumnB
Name1 / 12
Name1 / 11
Name1 / 12
Name2 / 100
Name2 / 105
Name2 / 105
Name2 / 98
Name3 / 14
Name3 / 14
Name3 / 2
NameX / ..
Since Pivot tabels cant do the trick as far as I know, i'm clueless of how to proceed.
I need to put together a number of worksheets, each worksheet is to record project information that's happening at our production sites and all the tables are uniform in design. There also needs to be one 'master sheet' in which all the information added on the lower sheets is automatically added.
So basically I need to have four tables on separate sheets that feed information in to one table on a further sheet.
I'm having a problem with data tables. Actually, not with data tables, but links to data tables. Calculation is set to be automatic and values within data tables update alright, but links to those values do no update. So you may have a certain value in one cell within a data table, for example cell B5, and a different value in another cell which is just a link to cell B5.
View 5 Replies View RelatedMy family owns a Fast Casual Middle Eastern/ Mediterranean Restaurant. I'm taking charge of making it as profitable as can be! Our plates are different combinations of kebabs, rice, Falafels, Gyros, salad, platters. So theirs tons of combinations.
I am Dead serious about knowing REAL plate costs:
I spent days entering price data, weighing meat, produce, calculating usable yields. I then built "component builders" using data validation drop-downs, and Index + Match functions to construct the costs of the recipe items,standard base items and side items. So I now know How much a skewer of Marinated Kebab Costs me, and all the Components that go into building a plate.
I've now built a "Plate Builder" (See Pic) to create the EVERY combination available at our restaurant. So when I'm done filling in all the builders, I'll have a whole lot of tables. I'd like to pull the name and plate cost from each table and create a big table organized by whether its eat in or take out. How do I do it.
TL;DR: Need to create a table that pulls data from several other tables....
I have multiple data tables that are linked to a site. My issue is that there are breaks in the data every 15 cells with a row that shows the column categories. I locked my top row so I don't need the recurring column categories at all, but when I delete them and refresh the data they reappear.
How to delete these rows? Could I possibly run a macro that deletes the rows upon hitting refresh? Or is there a simpler way?
I have a VBA code that goes to a specific Folder, grabs data from an Excel file, and then puts (data) in another Excel file (template with VBA) file as "Raw Data" tab.
Then my VBA refreshes 30+ Pivots from that "Raw Data" tab.
Now "Raw Data" is stored together with 20+ Pivots in one Excel file.
Challenge: Is there a way to keep my "Raw Data" in the external Excel file and simply refresh my Pivots by only "pointing"/linking that source?
I have 2 columns of data:
Column A is SKU #
Column B is Bin Capacity
I would like to take each SKU and its corresponding bin cap and arrange them into a 16 column and 9 row table on another sheet.
For example A1, B1, A2, and B2 of the old sheet, should become A1, B1, C1, D1 of the new sheet. Also I need there to be a line gap between each table.
Anyone know how to do this?
This is what I got so far:
I have a Excel Table that will be sorted/identified by the first column of every row. In the Table, there are numerous columns calculating values for each unique item across the row. This will be a large inventory table. The calculations will adjust based on a unit value of 1, and if the unit value is changed calculations go through the rows
I want to accomplish several things:
1) Pull data to another portion of the sheet based on,
a) Selecting a unique item that comes from a drop down list based on unique item identifier in the table
b) pull values related to the unique item and put in cells near the drop down list (I tried an =if function but did not have any luck
=IF(G1=ZillaRac[Item Name],ZillaRac[MSRP],"MSRP Not Calculated") but it resulted in #value!
2) If possible by selecting my unique identifier, insert a unit multiplier that would go into the Excel Table and adjust the values calculated in the table to be brought back up to the cells in part 1.
Here is what i want:
It's something like updating data by merging 2 tables. New names adding in end of table but peoples who has new mails need to be changed. Clear explanation in attached image.
People 1 and People 2 are same persons in both tables.
How can Excel input automatically the "total price" in the first table after I enter "type", "width" and "height" and looking up the other reference tables (below). The referenced tables will have fixed width and height but in the input table will be entered any number within the minimum and maximum range. So the idea is to get the price of the closest highest combination of "width" and "height" on the referenced tables and apply the price:
Input table (I will input "type" by drop down menu, and width and height)
type
width
height
total price
[Code].....
I have a file with numerous data tables that slows down the file significantly due to it's large size. I would like to set the calculation method to Manual, but also be able to manually turn the tables on and off so that they don't slow the file down when I hit F9 to calc. Is there a formula or method to accomplish this? BTW, I can't use VBA.
View 5 Replies View RelatedI want to a macro or a function that can search for the value which corresponds to a cell and copy it to another cell. please refer to the attachment for more detail.
View 9 Replies View Related
