If i have two list of numbers one list in cells A1:a50 and one B1:b50 i want in c1:c50 to see me the same numbers
Also if i have more lists of numbers a1:a50 b1:b50 c1:c50 d1:d50 e1:e50 then in row F show me the numbers that they are in all lists
I posted my question on another site and didn't get very many responses. I've got a list of alphanumeric numbers that I'm trying to cross check against a bingo grid.
The bingo cards are in a PDF format, so here's a screenshot. There's a separate sheet for Red; White; & Blue.....
I have a spreadsheet with Company A's part numbers, then the number for the same exact part from their rival company or competitor. When searching on our website I need for the customer to be able to find the part even if we are out of stock of Company A's part, we can let them know we have company B's part and it is the exact same part. So I am cross referencing the numbers.
In my spreadsheet I have all of company A's parts in column A and their competitor parts in column B. In another column I have cross reference #'s I did with the concatenate function. Where it gets tricky is one part can have 50 or more numbers associated with it. So far, I have been doing the concatenate for each one. I haven't gotten very far as you can imagine. Is there a way I can have it check a column, then when it finds the same exact numbers have it put the numbers from column B all in the same row seperated by a space(my delimiter)? See image at link. I couldn't figure out how to post the spreadsheet. Maybe I am too new to attach files.
[url]
so in the example above, cell E11 has company A's part number, it's own number, then the number above it and the numbers below it. It has every number associated with 74679142.
I want to make a crosstable (7 columns X 6rows) but the thing is that I only have the total number for each row and for each column, how do I do to get the whole numbers of the crosstable?
View 9 Replies View RelatedAttached is example of what I'm trying to do. I want to use the drop-down on the "Master Tab" and have the corresponding price by promo_month appear in the yellow cells.
Currently its on July, but I would like to switch that to August etc and have new prices populate in the yellow cells. Prices come from the "table" tab.
Example 06.19.14.xlsx
Say column A has either numbers or text in each cell, I need a macro to only get each number in each cell that is not a duplicate of a number in any previous cell and list each number found down column B.
View 5 Replies View RelatedI'm trying to create a staff rota which will populate a rota randomly when prompted - I have been trying to find some way of connecting the random lists and the staff names, though this has proved difficult (to say the least!). How best to proceed? I'm also fully aware of the possibility that my present design will also double book people (place then on reception and telephone duty simultaneously).
View 3 Replies View RelatedIs it posible and how to create a list of barcodes from a list of numbers on the worksheet?
Strokescribe seems to have some ind of solution but the data can't come from the worksheet.
Does excel 2013 have a fence way to sort a list of alpha numeric numbers and alphabetize in this order A - Z and then 0 - 9.
Ex) Apple, Greg, Rob, Sand, 123, 126, 1000, 2001
[URL] .... If you take a look at the picture, what I'm trying to do is add the list of Prices up so that I can differentiate the amount received in $ and in £.
I need to add up all the numbers that don't have a $ in front into the 1 cell, and all the ones with the $ in front into another cell.
FEdLkuj.jpg
Is there any script that I generate 6 numbers of these numbers from the list? so they do not recur after another example 7,12,13,19,26,33
View 2 Replies View RelatedA
B
c
d
[Code]....
So the data I put in Is Cells A to D the reast are calculated for me. so for example E2 is looking down column D to see if the number matches the number in A2, If any of them do it will put the name in the corresponding cell in E2 (Or you ca use a different cell if you prefer) so in this case D3=A2 so B3 is the answer also in the cell next to it I need whatever number is the cell next to the answer so in this case C3
1- I have a list full with numbers in a column. And I want to count how many digits are there in every cells. If digit of the number is less than 7, macro will complete it to 7 digits by adding 0 (zero) at the beginning of the number.
Example: A1 = 85468... A1 value is 5 < 7 in this case our number will be 0085468 (total 7 digits)
PS: sometime, when numbers start with 0 (zero), excel delete all 0 value at the beginning of the numbers. Macro should prevent this error.
2- I have another list with 2 columns which arranged in order. I mean:
A1=xx1, B1=xx2, A2=xx3, B2=xx4, A3=xx5, B3=xx6... etc. One is at A column, next number in B column.
My request is I want to make this list 1 column as arranged.
A1=xx1, A2= xx2, A3=xx3, A4=xx4... etc.
3- Macro will add a new line to the end of the above list with this format:
Z00001xxxx
Z00001 is static
xxxx is number of the full cells of A column plus 1. It will be 4 digits. If count is less than 4, it will complete it to 4 digits.
Example: if there are 15 cells in the list, last line will Z00010016 (15 + 1)
Let me see if I can explain my question in an understandable fashion....
I have a table containing data for about 2000 ID numbers. Some of these numbers are unique and some are duplicates. I would like to convert the ID numbers into a consecutive list of integers while preserving the unique numbers. For example, if the first column of my table is currently:
ID#
18578
19644
19644
20247
20974
21361
21361
21419
I would like to change that to something like:
ID#
1
2
2
3
4
5
5
6
I need to know which records (i.e. which rows of data) are from the same ID# but want to remove the actual ID#.
I have to create lists of data in the following format:
123121321,12321215,121351213 (numbers with , with no spaces)
These numbers are sent to me in excel in columns and I need to convert the columns into rows using the format above.
The formula I have been given:
=INDIRECT(ADDRESS(((ROW()-1)*10)+2,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+3,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+4,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+5,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+6,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+7,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+8,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+9,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+10,1,1,TRUE,"SSOs")) & "," &
INDIRECT(ADDRESS(((ROW()-1)*10)+11,1,1,TRUE,"SSOs"))
currently I only get 10 numbers per row
I need at times for up to 500 numbers to be listed on a row. However even when I try to extend the formula it still only gives me 10 numbers per row and breaks up the 500 numbers to 10 per row.
Is there another way to combine up to 500 numbers in one row?
From a list of numbers I would like to delete values that have cents so only those transactions with a .00 amount are displayed
For example
34.95
21.88
21.00
56.00
45.77
Only those valaues ending in .00 will list. I tried filtering but I think there most be a function(s) string that might work or at least filter out the values with cents
I have 2 lists and need replace the numbers in List 1 if they appear in List 2. If they do not match to list 2 then keep the number in List 1. I have attached a excel worksheet as an example. So cell B4 needs to be replaced by Amount in Cell F9. Also Cell B7 needs to be replaced by number in F5.
View 3 Replies View RelatedBasically I order thrice a week from a certain supplier to fill the shelves of my shop.
They make available to me two spreadsheet dumps: an orders spreadsheet, with the following columns:-
order ref
date
unique product code
description
qty
9262
03/07/2013
622642
Fabric Dye
4
And an invoices spreadsheet with the following columns:-
invoice ref
date
unique product code
description
qty
122551
04/07/2013
622642
Fabric Dye
4
Basically I can see the potential here to find out what we've ordered but that which they haven't sent which would be so handy.
I have tried to vlookup() produce code against product code, this kind of works but below are some example drawbacks:-
1) no idea if I've ordered it ten times and they've only not sent it once, or if I've ordered it ten times and they've not sent it nine times.
2) no idea of dates - i.e. ordered this in June '13, October '13 and February '14 and they sent it in February '14 only.
I have two lists of reference numbers; I need to see if any of the references appear in the other list. I don't want them to be removed, maybe highlighted?
I have made them into one list and used the duplicate conditional formatting but some references appear twice in one list and there is a lot of data so this is not good enough.
Is there any way to create a cross-validation rule between 2 cells?
For example,
Cell A5 will have one of the following values (AA, BB, or AB) ....
formulas i cannot get my head round at all i need e14 to work out how many times cell e1 appears in column h and in column b. so if yes is in e1. it then needs to look down column b and cross refernce it with column h and return the number of yes that appear in column b AND the same row in column h.
View 3 Replies View RelatedFirst as said in title i have no idea what these kind of tables is called in English and i didnt know how to formulate me when it came to the title (so hint me with a "right" name for this kind of table)
To the point i have alot of these cross reference tables. What i mean is table that have a horizontal titelbar (row) and a vertical titlebar (column) and in the table you have the diffrent stats or whatever that can be within it. Im having a really hard time expaining this so im just uploading a excel file with a little explanation of what im after.
I am trying to match a person's name in the A column (A3) with particular course requirements in Columns B through to BB (ie. B2). Not every person will have taken every course. I have the master list of data on another worksheet with 4 columns of data: A-Student Name, B-Course Name, C-Final Mark, D-Additional Notes.
What I would like to do is have Excel find the student name on sheet one as well as each of the courses listed from B-BB and check sheet two (master data) to find if the student name and course name appear on the same line. If it does I want it to return the value in column C (the final Mark).
I thought that I could do this with VLOOKUP but it only works with the left most column correct? I saw something about a VLOOKUP2 but couldn't find that option in Excel 2007.
I am involved with regional sales and have developed a spreadsheet to track various statistics and information regarding the various cities and clients.
On Sheet1 I have Column A for the city names. Columns B through R are various statistical information (all numerical) associated with that city.
I would like to use Sheet2 to quickly draw that information into a generic "printable" spreadsheet such that I type a city name into Sheet2 A1 and the rest of the cells automatically draw the information for that city into their respective cells.
Looking through other threads I thought that maybe VLOOKUP would be the ticket, but it's only returning a #VALUE! error.
I am having to figure out a particularly painful excel formula. What i need is a formula that will count the number of times an issue label in text shows up, but it has to match a certain time.
we can get the excel sheet to round the time to the nearest thirty minutes, so if a call comes in at 3:12pm it rounds it to 3pm. We label the calls that come in with a issue description also like "dvr issue." So i need a formula that will allow me to count the number of instances where if it says 3pm AND dvr issue it will only count it and give me a total in a specified cell. Is that possible?
Should I use the 'IF' or 'LOOKUP' functions to fill a cell based on the content of another?
If C2 contains 'Cork' I need D2 to read 'Munster'. Indeed I will need D2 to read 'Munster' for a number of different C2 values.
I need to set up a system for recording data into a certain way.
I a currently using this formula =IF(sheet1!A1:A20="H","Y","") and it kind of works.
Basically i want it to fill a cell in sheet2 with a "Y" if ony one cell between A1 and A20 from sheet1 contain a H. At the moment it only works if a few of the cell between A1 and A20 contain "H".
I have 2 tables. One has a listing of items and the work centers that they use and their associated hours. The other has the items and the amount we will produce each month.
What I need is to be able to calculate the number of hours for each item by month and then make a summary so I can see the capacity needed for each item, work center by month.
I have used the VLOOKUP and it works but when I overwrite the second table with new data the calculations get messed up
I am creating checkboxes and linking them to IF statements. I have figured it out all right but I really cannot go through 500 rows and insert checkboxes manually in each row.
View 9 Replies View RelatedI am trying to match/cross reference (check by ZIP CODE) two extremely large databases/spreadsheets in MS EXCEL. The vast majority of matches will turn up negative (no match) and only like <5% of all the rows/fields in my 31000+ excel sheet will actually match by zip to the records contained in another sheet (actually this second sheet is a large Seibel Sales database that I exported to Excel for easier access/manipulation) So it would be very tedious and a time waster to manually match the records by hand. My question would be, is there an automated way in Excel (or using any number of Excel addons such as ASAP Utilities etc) that would let the computer do the work for me?
Basically I have two large records, one that I exported from Siebel online to Excel, and the second one is the original work Excel database that I am to work on (ie find matches by zip code and mark them as matched)
Is there a quick way to do this? The power of the computer should really be put to use, but I just don't know /cant figure out how to go about this.
here is the screenshots and explainations if you don't know what I am talking about:
[url]
Just to clarify (I'm not sure I explained it very good)
Imagine two sheets
Sheet #1 <-(Seibel converted to xls) Sheet #2 (xls sheet to be worked on)
12345 --------------------------------54986 (mark as NO MATCH)
84596 --------------------------------25746 (mark as NO MATCH)
24578 --------------------------------12345 (mark as MATCH!)
96328 --------------------------------15789 (mark as NO MATCH)
45897 --------------------------------96328 (mark as MATCH!)
The problem is Sheet #2 is not only five rows, it is like 30000+ rows, and I
will have to do another 30000+ rows soon, so 60000+ to do by hand is very slow indeed.
Also, >95% of all the rows in Sheet #2 will be a NO MATCH with no matching zip codes to ANY of the zip codes of Sheet #1. So less than 5% will actually be a match, but the thing is I have to check ALL of them to actually know which ones match and which don't.
IS there a utility / macro or function in Excel that can quickly let me do this the automated way?
