Group By First 9 Digits

Apr 13, 2007

I have 2 columns, A and B. The data looks like this:

0040005A2002868000PMTo 164.40
003000005000037000PMTo 104.40
001000002002090000PMTn 188.35
002000002000015000PMTn 104.35
001000002000298000PMTn 92.80
001000004001042000PMTo 78.00
001000004001050000PMTo 78.00
003000001002100000PMTo 97.10
001000004002115000PMTn 92.75

I want to have column J with values from column A grouped by the first 9 digits and column J with the totals for these groups. It would look something like this:

002000002 250
002000004 300
003000027 100
003000050 70
004000002 90
etc

View 9 Replies


ADVERTISEMENT

Number Formatting: The First Three Digits Will Be Separated And Then Subsequently 2 Digits

Oct 31, 2008

i need to format my numbers in the following format

10,00,000.00

the first three digits will be separated and then subsequently 2 digits

View 2 Replies View Related

VLookup Only Right Most 4 Digits Of The 6 Digits Sequential Numbers

Apr 30, 2014

I have the following working great, but would like to see it refine a little, as the data vlookup is 6 digits, but i only needs the last 4 digits is enough for me to work, my question is how do i go about adding that to the following function i have implemented and working fine.

=IF(ISERROR(VLOOKUP(B4,' cmfs01home$peter[tracker data 4-25-14-a.xlsx]ControlSheet'!$B$2:$F$301,4,FALSE)),"",VLOOKUP(B4,' cmfs01home$peter[tracker data 4-25-14-a.xlsx]ControlSheet'!$B$2:$F$301,4,FALSE)

View 12 Replies View Related

Remove First X Digits And Last Y Digits From A Cell

Sep 25, 2009

I am editing a wine database which contains a vast amount of data, one column has the wine name and sometimes the vintage year in the begining or at the end of the cell. Sometimes the year is made of 2 digits (03, 05, ..) or 4 digits (1978, 2004, 2005, ...).
Is there a way to remove this vintage year form the string?

to make matters worse, there is often a single quote/apostrophe in front of the vintage year, which is driving me mad as 98% of the time it is one of these hidden ones that cannot be deleted using the find/replace function.

examples are like below:
De Wetshof Finesse/Lesca Cahrdonnay ‘07
De Wetshof Sauvignon Blanc ‘07
Lord Neethling Cabernet Franc 2002
Lord Neethling Pinotage ‘01
Bouchard Finlayson Tete de Cuvee Pinot Noir ‘07
Jacobsdal Pinotage 1994
Zondernaam Sauvignon Blanc 2007
Tokara Red
1976 St Emilion
03 Tokara rose
Plasir de Merle Cabernet Sauvignon ‘05
DuToitskloof Pinotage/Merlot/Ruby Cabernet
1999 Tradition Juracon 375ml

I have been searching the Internet for the past 2 days without luck on how to delete the end of string vintage year.

I have had some luck with the left side, as in:
=IF(ISERROR(VALUE(LEFT(B2,SEARCH(" ",B2)-1))),B2,MID(B2,SEARCH(" ",B2)+1,LEN(B2)))

As I am not an expert with Excel, I have no idea on how to use VBA (every time I have tried even basic things, I failed) nor even sure how the above funtion works (found it on another site).

I thought I could acheive my goal in two steps, first removing the left side vintage and use this partial result with the RIGHT equivalent funtion, but it simply is not working!

View 14 Replies View Related

How To Remove First X Digits And Last Y Digits From A Cell

Sep 25, 2009

I am editing a wine database which contains a vast amount of data, one column has the wine name and sometimes the vintage year in the begining or at the end of the cell.

Sometimes the year is made of 2 digits (03, 05, ..) or 4 digits (1978, 2004, 2005, ...).

Is there a way to remove this vintage year form the string?

to make matters worse, there is often a single quote/apostrophe in front of the vintage year, which is driving me mad as 98% of the time it is one of these hidden ones that cannot be deleted using the find/replace function.

examples are like below:
De Wetshof Finesse/Lesca Cahrdonnay ‘07
De Wetshof Sauvignon Blanc ‘07
Lord Neethling Cabernet Franc 2002
Lord Neethling Pinotage ‘01
Bouchard Finlayson Tete de Cuvee Pinot Noir ‘07
Jacobsdal Pinotage 1994
Zondernaam Sauvignon Blanc 2007
2003 Tokara Red
1976 St Emilion
03 Tokara rose
Plasir de Merle Cabernet Sauvignon ‘05

I have been searching the Internet for the past 2 days without luck on how to delete the end of string vintage year.

I have had some luck with the left side, as in:
=IF(ISERROR(VALUE(LEFT(B2,SEARCH(" ",B2)-1))),B2,MID(B2,SEARCH(" ",B2)+1,LEN(B2)))
As I am not an expert with Excel, I have no idea on how to use VBA (every time I have tried even basic things, I failed) nor even sure how the above funtion works (found it on another site).

I thought I could acheive my goal in two steps, first removing the left side vintage and use this partial result with the RIGHT equivalent funtion, but it simply is not working!

Does anyone have an idea on how to help with this?

Ideally I would love to cut the vintage year, whether 2 or 4 digit, whether on right or left of cell and paste it in another cell, so to avoid manually doing it.

However, this is surely too complicated to do, so iwould settle with just deleting the vintage year and manually typing the vintage in another cell.

View 9 Replies View Related

VBA To Search Group Of Cells And Input Message If Value Is Missing From Group?

Mar 17, 2014

I am trying to write VBA code that groups cells between blank rows and inserts a message if a string of text (a name) is missing from the entire group. I want the code to search cells in column A and group the cells between blank cells. Use the name I input in an input box as the search criteria and insert a message in the Column C next to the last blank cell in the group and then move on to the next group and highlight the cell in red with bold text. I am including a spreadsheet with an example of what the sheet should look like before and after the code is run.

View 11 Replies View Related

Pivot Table Group :: Group By Integers And Not By Months, Years Etc?

Nov 12, 2009

I have date fields as column labels in a pivot table. When I try to group them I'm only given the option to group by integers and not by months, years etc.

I've had a look at the format settings of the column and they are formatted as date fields.

View 9 Replies View Related

Insert Group Header Based On Consistent Value Within The Group?

Feb 10, 2014

I have several groups of data in the same sheet. Each group has two blank rows above the first row of data. Each group has column "B" in common (e.g., "Phone" in the example attached). I'd like to add a title/label of "Phone" to the first column in the row immediately preceding the first row of data for that group.

View 1 Replies View Related

Use The Group Box To Group A Number Of Option Buttons Together?

Jan 17, 2013

how to use the group box to group a number of option buttons together.

Question is, lets say, i have a total of 4 group box. if i want it to work in such a way where by once 2 option buttons are being selected (1 option selected from each group box), the rest of the option buttons in the 4 group box will be greyed out. is that possible?

View 2 Replies View Related

Convert A Group Of Numbers To A Group Of Letters

Aug 9, 2006

My store uses a retail price "code" wherein a price like 99 in expressed as a
code like "PP"--exchanging the number digits 1-0 as letters.

Is there a function to do this automatically?

View 9 Replies View Related

Apply Row Count Of Sub Group In Each Row Of Sub Group

May 28, 2014

In Col X of the attachment, I have manually entered the count of the rows within each sub group, as determined by the counter in Col W. How can I do this automatically? I need this because when I filter the spread sheet by Rank, I need to know how many selections, of the filter Rank query, were in a sub group of ? number.

View 2 Replies View Related

Sort And Group Names Then Sum Associated Numerical Values For Each Group - Sort Total

Apr 18, 2014

I have this massive spreadsheet, with the maximum rows excel will fit on one sheet. In the interest of simplicity, this is what the raw data looks like:

Last First Amount

Jones Jim $1000
Jung Joe $700
White Jon $100
Jones Jim $200
Jones Jan $300
Jung Joe $800
White Jon $200

What I want to do is automatically get excel to group all the same names together. Then I want it to sum all the values for each name, then order by largest total value for each person, then order that by name alphabetically. In other words, the above would look like this after the sort:

Last First Amount
Jung Joe $800
Jung Joe $700
Jones Jim $1000
Jones Jim $200
Jones Jan $300
White Jon $200
White Jon $100

Or, if necessary, there could be sum total rows under each name...although I don't have spare rows, so IDK. I could delete some rows if I had to, but would rather not.

View 1 Replies View Related

Change Color Of Group Of Cells Based On Data In Another Group Of Cells

Jan 12, 2014

Conditional formatting. I want to change the color of a group of cells based on data in another group of cells. Example:

If cells G8 and G9 (which are merged) are between 80% and 94%, then I9,I10,I11 (which are merged) will turn Yellow. Also, under the same scenario, IF G8 and G9 is greater than 94%, then cells I9, I10, I11 will turn Red.

View 2 Replies View Related

Find Max Of Last Four Digits?

Jul 12, 2014

I can't seem to find a way to find the max of only the last four digits of a cell, matching the first 8. As an example:

I have thousands of cells in a column like this, (and I can't add any columns to the sheet), and I have a cell with the first three digits and the second three digits. So, out of all these numbers, I want the MAX of ONLY the numbers with the first 8 digits of "800-123-". Also, the decimal on the end is how many times the number was called, and any the decimal and any number after it is to be ignored. The answer would be "800-123-0024", or "0024", I just need a faster way to find it without searching for it.

888-555-0099.2
800-123-0022.3
555-333-0474

[Code]....

View 9 Replies View Related

Delete The Last 2 Digits

Nov 11, 2008

I have a long list of 4 digit numbers:

e.g.

0234
2434
6566
4566
6785

But I only want the first 2 digits (I need the last two digits deleted). I don't want to just divide by 1000 as this will leve me with a decimal. The numbers are in text format as some of them begin with a 0. So it would be:

02
24
65
45
67

View 5 Replies View Related

Vlookup First Two Digits

Mar 6, 2009

I would like to take the first three digits of column A and do a lookup of column B that would return the corresponding number from column C. For example, if I entered the formula for 103PH, the lookup would find the 103 in column B and return "2775.00" from column C...........

View 4 Replies View Related

Must Have 3 Digits In A Cell

Nov 24, 2009

I was wondering how do you format a cell so that when i enter the number 7 it automatically sets it at 007 and for like 10 it would be 010 so a must have of 3 digits

View 5 Replies View Related

Add Sum Of Digits In A Cell Using VBA?

Mar 9, 2014

adding the sum of digits in a cell using VBA. For eg: in A1 if I have 12345 I need the sum of 12345 (15) in cell B1.

View 3 Replies View Related

Cut Off Digits Without Rounding

Dec 4, 2012

I have a number 53.30242 in a cell a1. How can I just make it 53.302? I don't want to round it to 3 decimal place, just keep the first 3 digits.

View 6 Replies View Related

Format SSN To Last 4 Digits

May 2, 2013

I have a column with social security numbers, i.e. 555-33-2222 and I need to change to show only the last four digits, i.e. xxx-xx-2222. Can this be done in excel?

View 4 Replies View Related

Significant Digits

Aug 24, 2008

I have a column of numbers, all with varying numbers of digits. I want to make them all have only 4 DIGITS in total (regardless of where the decimal is located... so there could be 4,3, 2, 1,or, 0 decimal places). I just want to make everything the same number of digits.

View 9 Replies View Related

Count 10 Digits

Sep 7, 2008

In this worksheet , in Columns B4:F2615, there are rows of digits that range from 1-36, I have a need to find the 10 digits that were drawn togather the most. I have no idea how to do this in Excel or can it be done? ....

View 9 Replies View Related

Pair Of Digits In Col B

Nov 4, 2008

I have a pair of digits in col B, that I would like to match with the digits in in col D, and display those matches in col E. If possible I would delete the duplicates in col E, and show results in col F.

View 9 Replies View Related

How To Give Last 6 Digits Only If A #

Jun 24, 2009

I have a formula now that is =right(C2,5)+0 that is working well. However the data has grown and sometimes there is also 6 digits now instead of 5. So I need it to pick up either one 5 or 6. When I change the formula to 6 it works but picks up a / which happens to be before the 5 digit # sequence when there is only 5 digits. It works great for the 6. Is there another way around this so I only get the numbre digits if there are 5 or 6 and not the /. Maybe an if statement. I've tried several ways but none work right. The only other thing I can think of is to get it as above with the =right(C2,6)+0 and then afterwards to a find and replace and remove the / from the data. I was just tryign not to add an extra step to the process. Any ideas please?

Example of the data in coloumn C2 is:
15/2000/4567/NA/NA/97305or with 6 digits at the end15/2000/4567/NA/NA/973052there is always just 5 or 6 digits at the end that I need.

View 9 Replies View Related

Remove The First 4 Digits

Oct 8, 2009

1. Remove the first 4 digits from each "Appeal ID"

2. Insert a new column (first column) called "Chapter"

3. Run a v-lookup down the new column against a file that is stored on my desktop. The v-lookup will cross check the Appeal ID against the file to identify the Chapter

4. Sort the data alphabetically by Chapter

5. Create seperate Excel files for each Chapter ...

View 13 Replies View Related

Filter Last Two Digits

Jul 8, 2003

I tried every filter function I know of, to no avail, and am yielding my stupidity to the forum. I have this series of numbers as an example:

456912
789547
785171
658712
968712
369874
258741
127812

All I want to filter is all values ending with 12.

View 9 Replies View Related

24 Digits Mod Function

Jul 31, 2007

how can i do this in excel 2002?

for example.....

mod(95000000922019182020281000,97)=24

but in excel im getting the value as 0
also when i type the 24 digits its show like ..... # NUM!

if i put the function like =A1-FLOOR(A1,B1) its working for 13 digits but not for 24 digits.

View 5 Replies View Related

Replace First X Digits

Dec 14, 2007

I've recorded a macro to replace the Australian telephone number area codes (at the beginning of each phone number) with international dialling codes. I also need to replace the first two digits ONLY of mobile ( cell) phone numbers which, in Australia, all begin with "04" (see last part of macro code below - column L:L).

With the code the way I've recorded it, if it finds "04" in another part of the number (e.g. 0411 104 111), the second occurrence of the "04" will automatically be replaced with "614" as well which I don't want. So I need some code to add so that the macro only searches and replaces those first two digits in that column. I hope I'm making sense?!

Sub ConvPhNo()
'
' ConvPhNo Macro
' Macro recorded 12/12/2007 by xxxxxxxxx
'

'
Columns("C:C").Select
Selection.Replace What:="(", Replacement:="", LookAt:=xlPart, _ ...........

View 9 Replies View Related

Copying 2 Digits From A Cell

Aug 18, 2014

I found a formula that would copy only the last 2 digits of a previous cell and put it in a new cell. For example below, I want the cells to the right of the below to be:

12345 45
26548 48
21854 54
211ae ae

I thought it was a =right or something.

View 1 Replies View Related

How To Remove First 2 Digits If They Are Certain Numbers

Jan 29, 2014

I have a excel file, I need to remove the first two digits if they are certain numbers, such as 12. For example, if the number is 12987654, then I need remove 12, and it will be "987654" , but if it is not 12 in the first two digits, then keep it no change, for example if it is 345678, then keep it.

I barely work with Excel formulas, now I need connect the excel file with my Database table. I need to make the file matches the DB.

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved