I have a database with 65536 rows and A-BZ columns
What I am wanting to do is, on another worksheet be able to select identifying data from one or more columns from the database but have it display the entire row.
For example
WCP28 SX
WCP28 TX
WCT36 SX
WCT36 TX
In this example I would like to, on another worksheet, be able to create a drop down where I could select WCP28 and it would show me both the TX and SX row. Or I would like to be able to select WCP28 SX and WCT36 SX and see the rows next to each other. If it were for me I would just use the auto filter and manually compare but this is for somebody even less inclined than I am.
I have two tabs in a workbook. Each has a column containing a unique numerical identifier. What I want to do is create a macro that will loop through the column on tabA, verify it against the column on tabB and when a match occurs delete the row on tabA. The end result should be that only the new items on tabA will remain. Let me know if this is sufficiently clear or still a bit vague.
I'm trying to display only rows that contain data.
I have a work book that contains several work sheets. The work sheet are named by department, I'm trying to display only rows that have data and to which department they belong. I have 7 columns of data with a header. The header names are the same for all work Sheets.
The headers are Customer#(A1), Order#(A2), Qty(A3), Purchase Date(A4), Comments(A5), Promise date(A6), and Ship Date (A7).
I only want to display cells that have data from the work sheets onto a summary page.
Need in displaying data by user input criteria, mulitple rows of display also need to display to blank out when no criteria is input. In example spreadsheet the criteria cell is B6 in "Results" page and needs to pull in data from the "Database" page.
Data to appear on one of my sheets. I have a database of family info, where each family has an ID in A on the "Mems" sheet, and each child is listed separately on "Children" sheet and they each also have the family ID listed for each child in A. On the Mems sheet all the IDs are unique. On the Children sheet, as there can be multiple kids per family the same ID value can appear multiple times in A.
How do I get the Mems sheet to show all the kids names (in B on the children sheet) either in 1 single cell (concatonated) or in a series of cells apended at the end of the relevant data row on the Mems sheet?
I am not very smart in Excel. I have problem with pulling data from the following range of rows.
A 1 Apple 2 Banana 3 4 Grapes 5 6 Pomegranates
Expected Result: Apple, Banana, Grapes and Pomegranates
scenario: One or more than one of any of the 6 rows can be blank. I need a formula or procedure that pulls together data from A1:A6 automatically and adds "and" between the last two values. See the expected result above for an example.
I have two columns of items that need to be compaired. What I need is the items in column B that do not match any of the items in column A need to be put in Column C. How can I do this? The Data in Column A can not be re arranged. Column B can be sorted.
I have a spreadsheet and I'm comparing data from a room booking system and a spot check on the room to see whether it's actually used. I have a column which I want to show whether the information agrees.
column G - I have a group size recorded that should be in a room. column H - I have "yes" or "no" as to whether there was anyone in the room when it was checked column I - I have the size of the actual group in the room.
Is it possible to create a formula that will fill in column J with the answer yes or no depending on whether it agrees.
It doesn't matter whether the groups sizes match, it's just a case of yes, someone was booked into the room and there was someone in when it was checked
OR
No, there was a booking supposed to be in the room but there was no one in it when checked
OR
There was no booking but there was someone in the room when checked.
With a set of data in colums by quarter and department names in by row I need to answer build a formula that answers simple questions like "what department had the fewest sick days per quarter?" and which department had the most sick days per quarter?"
I would like to insert a function in spreadsheet A that looks up data in spreadsheet B based on _two_ comparison values.
The two comparison values in spreadsheet A would always be in the same row and the corresponding match in spreadsheet B would also have to be in the same row.
For instance, if the comparison values in cells A2 and B2 of spreasheet A match with the comparison values in cells A30:B30 of spreadsheet B, then show the value that is in column C30 of spreasheet B.
Excel macros. I'm trying to figure out a script that will look at alphanumeric numbers in column A (A2 --> for example) and compare it to the number directly below it and above it. If it matches either, do nothing, but if it is different, delete the entire row that the different number resides on.
An example of the data would be:
A
12A34A5 12A34A5 67F86F3 - when it encounters these rows I need to delete. 23G45G6 23G45G6
The sheet is a couple thousand lines in length, so I can't deal with it manually or even with sorts, due to the other data in the other columns.
I am trying to figure out a way in excel to graph some data I have in a new, sophisticated type of chart. My data is new square foot sales divided by new square feet (for a retail store). It is a dollar amount. I also have this data as a percentage change year over year. If I want to compare two stores with different data -- what would be the best way to present it on a chart. Is there a way to show this on a 3-D plane so that both companies have the data in the same chart up against each other?
I have some data stored in a database and when it is extracted into excel each entry's data is presented in both horizontal and vertical formats. e.g.
Entry1 value1 value2 value3 value4 value5 value6 Entry2 value1 value2 value3 value4 value5 value6 I have a macro that will transpose the vertical data into horizontal format but if there are not the expected number of 'value' items below the entry it will not work correctly. If in the above example Entry2 only had value4 and value5 below it the macro would fail. What I need is a way to validate that each entry has the correct number of values below it before the macro is run and some way of highlighting or drawing attention to these problems.
I want to be able to do, however. I want to make a spreadsheet that has all the equipment items preloaded into it, along with the quantity that we're supposed to have of said equipment. Then, I would like to import the data from the text file, having it match items in the spreadsheet to their equivalent in the imported text file, and then bring in the quantity scanned. That way, I can use some conditional formatting to show when there is a miss match between "Required" and "On-Hand" quantities.
What I don't know how to do, is get Excel to only import the name of the item, and the quantity scanned, instead of all of the information that the scanner spits out. Also, I don't know how to make it match the name, with the one preloaded into the spreadsheet, so that the correct quantities are matched up. Here's an example of what the scanner spits out: FIELD WIRE,6145-01-155-4256,[Scanned Quantity Goes Here],N/A,N/A,RESPONSE TRAILER,,
The scanner creates records like this the first time you scan an item. So if you scan Field Wire first, it's the first record. However, if you scan it fourth, it's the fourth record. How do I make that match up with a predesigned spreadsheet?
What's odd is that if I create the formulas above independent of the specific formula data (O5, P5, and Q5) and just type in numbers, those formulas do exactly what they should. Can I not compare data returned though formula calculations?
I'm interested in comparing the 2 columns of data as attached in the SampleData file, for eg. First customer bought 5 items and customer 2 bought 3 items. I'd like to show the difference which customer 1 bought and customer 2 did not buy. Will need to use that via VBA And also for loop if possible!
I have 2 Data tables in 2 sheets of the same workbook. I want to compare my column A of table 2 with column A of table 1 and delete any rows of table 2 where (column A of table 2 has a value which is not in the column A of table 1)
In Excel I used the Vlookup function and deleted any rows which had Error in result of the formula. May I know how to execute this in VB
I have setup a dropdown list of different regions that cover our company and what I want to do is that when one of those regions is display only the rows from that region are displayed in the list either on the same or a new worksheet.
I know this can be done by adding a filter to the column in question but I am dealing with very basic PC users so to make their lives easier it would be great to use the drop down box option.
I have this sheet at work which contains a bunch of courses my employer runs, alongwith the dates and other details (which spans columns A through O)
Course-rows which require action/follow-up are highlighted with a certain color. The "completed" course-rows have no fill. Note: the courses requiring action may have a past date, the only distinguishing feature is the highlight color.
Is there any way by which i could generate a pop-up box of some sort which would display the "highlighted" rows (maybe from A through I or something).
I could add 'follow-up' flags on another column if the highlight color won't do.
There will be many orders in this list and each orders will have several appendixes. I want to able to see and print the orders with their first appendix row but I want to do that when I need.
After running advanced filtering through VBA like that:
[Code] ........
Excel returns a couple of rows which fit the Criteria. How to display these rows in a textbox in the userform? (all I've managed to display in it is one cell).
1) Is it possible to hide all cols save D & E and also all blank rows in Col D
2) I would like to have an initial If statement saying stating that if the answer to the question in D2 is Y then don't display anything else other than the info in B2 & B3.
£) the sorting seems to have gone funny - not sure what I've done here, I'd like it sorted by phase (col A)
4) where there is a blank in the cell under a question (for example D3) - this means that the same question applies and that multiple docs should be returned (e.g for Q1 D2 docs 1 & 2 display, for qu D37 docs 36 - 39 display)
I am trying to display a certain number of visible rows, using AutoFilter. My idea is to use a helper column that will 'number' each visible row, but I'm not quite sure what functions to use for this.
I want to be able to view only the rows containing search criteria.
For instance, if I search for the word "sandwich", I only want to view rows containing that word. How do I do that? Or... Is there a way to group the rows containing the word "sandwich" together.
I have about a thousand rows that I need to sort and distribute to different worksheets. I don't want to work through the list moving single rows or small groups of two or three at a time.
I need the selected rows to be displayed on a new sheet. The idea being that I have a quiz with hundreds of questions and I only want a random selection to appear when I execute the macro. I plan on hiding the original questions and only displaying the randomly selected ones.
This list on sheet 1 has the persons employee id and various bits of info. There will be multiple rows with the same employee id. This data is not sorted in any particular order, but I'd rather leave this sheet untouched.
On another sheet, I would like the user to simply type in a number for the employee id (I don't want to use msg box, just a cell is fine, maybe a button form to click search to run the macro) and it lists all their hits, however many rows that may be. Again I can't sort the original list so the employee might have hits scattered throughout the main list.
So they would enter the employee id and below that they would get all the rows with all the above data where they get a hit. One concern I have is say the first search for employee 123456 turns up 18 results. Then they search for employee 123444 and it only returns 8 results. I don't want the last 10 rows of employee 123456 to be still present. Basically a search that searches another sheet and returns any hits, and it clears the list of previous search results so for each search you only see the results of that search and not old data.
I can view the data how I need to by conditional formatting and pivot tables but HR wants to get rid of all rows that: only have 1 accounting line per document such as Doc MI310712 in attached sample So I only want to display records if there is more than 1 accounting line for each document
My understanding of Excel is that in order to get rid of rows that a VBA macro is needed since there is no function to do it. My VBA is limited but is there a way to specify criteria in a filter to hide the rows using a formula?
I have some checkboxes that filter rows and a text box that should display the number of filtered rows:
If checkbox1 Then Range("F2").AutoFilter Field:=6, Criteria:="<>0", Operator:=And TextBox1.Text = "No of Records: " & Selection.SpecialCells(xlCellTypeVisible).Count
The text box is showing 1669380 record when there is only about 250.
I'm trying to compare 2 worksheets with the same headers(NAME, ADDRESS, CONTACT NO...). What codes should I use that when there is a duplicated row in worksheet A and worksheet B, it will be deleted and when there are 2 names with different addresses and/or contact no, the whole row in file A will be obtained. Output should also be in another worksheet. It goes like this.
worksheet A NAME ADDRESS CONTACT NO JOE ABC 123 MIL XYZ 567 NIK LMN 234 NED QRS 456
[Code]...
So the output should be:
worksheet C NAME ADDRESS CONTACT NO JOE ABC 123 MIL XYZ 567 NIK LMN 234 NED QRS 456 JIM JKL 345 SAM FGH 789
