Is it possible to delete a column of numbers then replace those numbers from adjacent columns and the adjacent columns are then filled with the numbers adjacent to them
View 9 RepliesIn the attached workbook I have groups of numbers in Col G
In col H I would like to trim col G to just one each of the numbers where there are duplicates (as shown).
Each group of numbers will have a blank row between.
I have a column of numbers (column A) which I'd like to compare to an adjacent column of numbers (column B) and, by row, determine if the number is larger.
If it is, I'd like to place a different colour on the cell. What is the formula I need to use to accomplish this?
Ex:
Col A Col B
30,000 5,000 - A is larger than B, therefore A is formatted RED
20,000 20,000 - A is the same as B, therefore no format applied
10,000 15,000 - A is less than B, therefore A is formatted GREEN
I have taken over this spreadsheet for my work, and it is basically a statement in excel. What I want to do is find a list of invoice numbers in column B populated from a remittance, and then replace column F to say a specific thing depending on check number and date paying for that invoice. So if a check printed today I would have it replace column f to say paid 1/31/13 check # xxxxx. Currently I am searching for each invoice indivudually and then replacing with check number and date. There are about 200 invoices per month that I deal with, and it is a big waste of time!
For example from this
invoice #1 / reconciled (DATE)
invoice #2 / reconciled (DATE)
To this:
invoice #1 / paid (DATE) check # (xxxxx)
invoice #2 / paid (DATE) check # (xxxxx)
One has numbers the other has "YES" or is blank. I want a formula to look at the second column and where there is a "Yes" then count the number in the same row in the first column.
View 4 Replies View RelatedI want to ask that I have got a workbook with different number series i want user form where i can enter its start number and end number and then it finds and delete shift cells up said series number i have entered in user form please see mentioned below example.
Series
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
and i want to delete 1 to 5 numbers delete to shift cells up.
I have attached a pdf of part of my data. I wish to delete those cells in column 2 containing values between 9044795 and 9372402; then have the column below move up (into the cell deleted). Its a large data set so doing it manually is not an option.
After doing this the data across the rows should line up. I have tried using the replace and filter functions but I can't make it work. I've tried some VBA but my knowledge is wanting.
is there anyway i can delete all numbers from a column, leaving only alpha characters?
View 8 Replies View RelatedHow would you set up a macro to delete numbers in a spreadsheet in one column that appear in another column or worksheet?
In other words, if I have the numbers 1,2,3,4,5,6,7,8,9, 10 in one column and in the same column, or another column or worksheet I have the numbers 1,2,3,4,5 - I need a way to delete 1,2,3,4,5. I know how to delete duplicates via the advanced filter but I want to have the duplicate and original deleted.
How is it possible to delete the phone numbers which include 39 like (323940, 393312, 124039, and so) suppose all the phone numbers are in cell B
View 9 Replies View RelatedI have a mass of data which look something like this:
table removed
and I require the ranges of reference numbers to be listed in a column one above the other, which requires inserting new rows. I also need the date & description columns copied down into the newly inserted rows.
So basically for example I would want the top row to now read:
table removed
and then apply the same procedure to the other ranges below this.
This is what I need:
Columns B, C, D & E are all populated with 3 digit numbers.
I would like column F to automatically populate with any of the 3 digit numbers that share two numbers, i.e.
F2 might look like this (using 00 as the pair):
001, 040
F3 might look like this (using 01 as the pair):
701, 051, 110, 001, 120
F4 might look like this (using 12 as the pair):
123, 721, 281, 912, 112, 120
etc...
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 have a column of variances, these contain both negative numbers and positive numbers. I want to gather a sum of all the negative numbers and positive numbers separtely. Basically saying all the positive overeages = this amount And all the negative shortages = this amount. you can see the attached sample.
View 3 Replies View RelatedI want to delete rows in whole list and numbers of rows to be deleted I have in for example C column. How to do it?
View 9 Replies View RelatedI'm working on a spreadsheet with about 400 rows and 10 columns. All the numbers in the columns are formatted as text, which doesn't allow me to use AutoSum. I have selected the cells and formatted them as number with no decimals, but AutoSum still doesn't work because the formatting hasn't changed and they're still formatted as text. I know if I double click each cell, the cell changes the format from text to number, but that will take a lot of time to change 400 cells in each of the 10 columns. What can I do to quickly reformat the cells from text to number?
View 1 Replies View RelatedI'm trying to pull some numbers from another column. I want to pull the numbers that have an X separating them like 7X125, 48X192, and 27X90.
Example:
FA, VF-2000-3-7X125-18-A, AFS
FA, VF-2350-48X192-6-RGB, FC
FA, VF-2020-27X90-18-A,RFI, FEX, ACP, 2IT
Set cell fill color based on number of adjacent cell
I am doing a research with the following information. In column B, I have the name of the buyer company, column B cells that contain numbers 1, 2 or 3 opposite the company name in column B depending on certain characteristics. I would like to mark the companies in column A in different colors (or some other recognizable way) as described below.
columns result
col A col B col C
7 Materials Inc 3
7 Materials Inc 2
6 Rayon Inc 3
6 Rayon Inc 3
6 Rayon Inc 3
8 Commun Corp 1
8 Commun Corp 2
8 Commun Corp 3
8 Commun Corp 2
8 Commun Corp 1
I would like to have on output in column A as shown above according to the following criteria:
4: if there are only 1 in column C opposite the specific company in column B
5: if there are only 2 in column C opposite the specific company in column B
6: if there are only 3 in column C opposite the specific company in column B
7: if there are 1, 2 and 3 in column C opposite the specific company in column B
8: if there are 1 and 2 in column C opposite the specific company in column B
Any suitable function in excel or Macros that can work with the formatting, because I have tens of thousands of companies and it will take too much time to do it manually.
I would like to copy rows based on the value of another cell.
For example turn this
code qty
apple 3
banana 4
orange 2
into this
apple
apple
apple
banana
banana
banana
banana
orange
orange
I have a fairly large spread sheet where I need to count the number of instances where the number in one column matches the number adjacent to it in a second column.
View 9 Replies View RelatedI have several numbers that are contained in non-adjacent cells. I need to create a cell that will evaluate the cells individually to make sure that they are within a certain range. Once that is done, I want the remaining numbers that are within the range to be averaged. How the heck can I accomplish this task? SUMIF and COUNTIF will not allow me to use non-adjacent cell ranges.
View 9 Replies View RelatedThese telephone numbers have changed so I want to add new numbers to the numbers in column with red color
View 3 Replies View RelatedI have two columns that contain IDs and Values for all our categories in a business directory:
i.e.
1 cars
2 home
3 boat
etc... (121 total)
I need to use the above two columns as a reference and use them to replace the value in the directory that is text (col 2) with the ID (col 1). I have a few thousand entries and need to find a way to quickly replace the text category field with the numerical id from my parent table.
In my first column I will be entering both negative and positive numbers. In the cell next to it I need the amounts to all show as positive only. So both negative numbers and positive in the first column will all show as positive in the adjacent column/cell.
View 2 Replies View RelatedI currently have: ...
View 8 Replies View RelatedI have a bunch of phone numbers that I'm trying to reformat.
Say I wanted to replace any phone number like this: 1##########
With this: ##########
Keeping the numbers intact, just getting rid of the 1 in front of them. Can't figure it out for the life of me.
Hey Have a spreadsheet with about 150 worksheets in.
In Column B of each worksheet is a list of packages
e.g.
Value Added Processing
Business Viability
Environment
Climate Change
I need to add into that other cells to further divide those packages so it will end up looking like
Value Added Processing
GA001
GA003
Business Viability
GA005
GA032
GA065
Environment
GA023
Climate Change
GA030
GA029
I have a separate worksheet which has the 37 different package types along ROW A from Column A to Column AH, then going down underneath each of the heading is the various code numbers as shown above.
Has anyone got a VBA code which will allow excel to search the worksheets, find the package name and then replace it with the package name but also insert the code numbers below it.
This is what I am using:
=VLOOKUP($A5,TEAM!$B:$MZ,23,FALSE)
When I select the cell formula extension button thing and drag it to the right, the formula copies exactly into the next cell. If I select multiple cells starting from 19-23 and then drag, excel just repeats the formula over, (19, 20, 21, 22, 23, 19, 20, 21, 22, 23, 19, 20...) This is seriously frustrating as I have to click into the formula bar on each cell and change the value of the column lookup.
I can't keep going manually as I need to go to about 300 cells...
I have a column of invoice numbers, formatted as text, some of which have first character "0" & last character "C" e.g. "012345C". I want to get rid of the "C" leaving e.g. "012345".
I have tried Find "C" / Replace " " with the undesirable result being that the leading "0" disappears too. Find "5C" replace "5" gives the same.
I would like to easily replace every character in column A with a blank, with the exception of - and a number. I am trying to get rid of all letters,:, / and other miscellaneous characters without having to delete them one character at a time.
View 7 Replies View Related