Posts by jpedges38

Re: Finding Specific Letter and Number Strings

Thanks Jindon that works great, it would be nice to see a formula for this though. I have been playing around with ISNUMBER(SEARCH and FIND along with using RIGHT, LEFT, MID, and LEN but I can't get anything that works

Say I have these two lines in Column A:

3051S2TG4A2A11X5AWA3WK1I5A1140
3051S2LD2AA1A1030DFF71CA00

I need to be able to have a formula in Column B that Finds the letter "A" when it is followed by four numbers, and then return "A and the 4 numbers."

So for the first line, the result in Column B would be "A1140," and the second line result would be "A1030"

Any help would be much appreciated.

Thanks,
J

Re: Macro Sort Alphanumeric Row by Row Alphabetically

Here is what I currently have. It works but it runs really slow. Is there a way to make it run more smoothly?

Option Explicit
Sub Makro1A()


Dim LastRow As Long
Dim FirstRow As Long
Dim iRow As Long


With ActiveSheet
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For iRow = FirstRow To LastRow
With .Cells(iRow, "A").Resize(1, 10)
.sort key1:=.Columns(1), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=5, MatchCase:=False, _
Orientation:=xlLeftToRight
End With
Next iRow
End With


End Sub

Here is an example of the sheet of data I have:

S2 L5 E5 CN


S1 E5 L5 CN

S1 L5 E5 CN

H2 L5 E5 CN
S1 M5 E5 L5 CN



S2 CN E5





S2 CN E5

H2 CN E5
M5 DF Q4 H2 B3 CN

Each two letter combination is in its own cell. I want the macro to sort each row one by one in alphabetical order. I can do this manually by using the sort options and literally selecting every row one by one and sorting it, but there has to be a way to write a macro to do this. The column range goes from A-T and there are a dynamic number of rows so I need the range to go to the last row with data in it. Any help would be much appreciated.

Thanks,
J

I have 3 pivot tables in my workbook with the same data source. Each pivot table is on a different worksheet. I want all pivot tables to automatically update the filter when one of the pivot table report filters is changed.

The macro I am currently using to create the pivot tables from my raw data is a combination of a bunch of macros I have stored in a blank excel book that runs on excel startup, so that I can use the macro on any raw data set.

If there as any code or formula I could use to auto update my report filters that would be extremely helpful.

Thanks,
J

Re: Macro Help: Finding Letter/Number Combos

Thanks Dave! that put me more on the right track!

Re: Macro Help: Finding Letter/Number Combos

But that would not work for a string such as: 3051CG5A02A1AH2E5M5CNQ4 because the Q4 is the last thing in the string, not CN. Is there a way to check if the CN is anywhere in the string? And if so, then to remove it from the string?

I have a set of data in column A as shown below:
[TABLE="width: 169"]

3051CD2A02A1AH2B9E5L4

3051CD2A02A1AH2B9E5L4CN

3051CG4A02A1AH2B9E5

3051CG4A02A1AH2B9E5CN

3051CD3A03A1AH3B3M5E5T1

3051CD3A03A1AH3B3M5E5T1CN

3144PD1A1E1B4M5CN

3144PD1A1E1B4M5

3051CG5A02A1AH2E5M5CN

3051CG5A02A1AH2E5M5

3051CG5A02A1AH2E5M5CNQ4

3051CG5A02A1AH2E5M5Q4CN

[/TABLE]

Each pair listed above with a space in between is the same item, but they are showing up as different items in excel because of the order of the letters and numbers or because they are missing something.

Is their a way to make a formula that looks in each of the cells with the items and removes the "CN" from the item if there is a "CN" located anywhere in the item. Then it would check to see if items have the same letters and numbers, no matter what order they are in. Then it would add a "CN" to the combinations of letters and numbers that are the same and paste that result in column B.

So the last pair would go from
[TABLE="width: 260"]

3051CG5A02A1AH2E5M5CNQ4>3051CG5A02A1AH2E5M5Q4>3051CG5A02A1AH2E5M5Q4CN

[/TABLE]

I do not even know where to start with this problem because they combinations of letters and numbers are many different lengths. Any help would be much appreciated.

Thanks,
J

I currently have this code in a macro, and I want it to run that formula over the length of the second row until it gets to the last occupied column.

Range("B2").End(xlLeft).Column.FormulaR1C1 = "=IF(R[2]C[2]=""Grand Total"","""",IF(R[3]C[2]="""",""QUOTES"",""ORDERS""))"

So I have two columns of Data, in the first column with the header "Quote Years", I have years listed. In the second column, I have the minimum year from the first column listed. I need to be able to make the header of the second column "Quotes (Minimum Year)". It needs to be a dynamic formula because the length of the first and second columns will change. Any help would be great.

Thanks,
J

[TABLE="width: 492"]

QUOTE YEARS

[TD="align: right"]2012[/TD]

[TD="align: right"]2011[/TD]
[TD="align: right"]2011[/TD]

[TD="align: right"]2011[/TD]
[TD="align: right"]2011[/TD]

[TD="align: right"]2011[/TD]
[TD="align: right"]2011[/TD]

[TD="align: right"]2011[/TD]
[TD="align: right"]2011[/TD]

[TD="align: right"]2011[/TD]
[TD="align: right"]2011[/TD]

[TD="align: right"]2012[/TD]

[TD="align: right"]2011[/TD]
[TD="align: right"]2011[/TD]

[TD="align: right"]2011[/TD]
[TD="align: right"]2011[/TD]

[TD="align: right"]2011[/TD]
[TD="align: right"]2011[/TD]

[/TABLE]

So I have data in column Q of my spreadsheet that has the dates for different orders. I need a macro that will search the date for "2011" and If 2011 is included in the date, then paste the value "2011" into column R. If anyone could help me out that would be great.

Thanks,
J

[TABLE="width: 380"]

COLUMN Q
*HEADER*

[TD="align: right"]03/02/2011 15:45:27[/TD]

[TD="align: right"]04/05/2011 14:11:39[/TD]

[TD="align: right"]05/16/2011 19:11:16[/TD]

[TD="align: right"]05/16/2011 19:11:16[/TD]

[TD="align: right"]06/29/2011 15:43:55[/TD]

[TD="align: right"]09/12/2011 18:16:06[/TD]

[TD="align: right"]09/20/2011 21:37:10[/TD]

[TD="align: right"]09/20/2011 21:37:10[/TD]

[TD="align: right"]09/28/2011 15:46:00[/TD]

[TD="align: right"]01/06/2011 23:36:43[/TD]

[TD="align: right"]01/21/2011 20:28:51[/TD]

[TD="align: right"]01/27/2011 20:03:04[/TD]

[TD="align: right"]02/01/2011 14:03:00[/TD]

[TD="align: right"]02/01/2011 14:26:20[/TD]

[/TABLE]

I currently have a macro that follows:

Sub CommandPrompt()
Dim sCellVal As String
sCellVal = Range("C1").Value


Application.Goto Sheets("Unique Model Numbers").Range("B1") 'Select the Customer dropdown cell
Range("B1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
MsgBox "Please select a customer. ", , "Select Customer"


Range("C1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""(All)"",""YES"",""NO"")"
  
  
    
If sCellVal = "NO" Then Application.Run "'Macro Storage.xlsm'!Open_Workbook"

The message box part works fine, but the If/Then statement asking excel to check the value of the C1 cell and if the value is "NO" to run the application does not work. Can anyone help me out with this?

-J

Hey, I am trying to use this code to check for duplicates in two columns, but I am getting a mismatch error whenever I try to run the macro:

Sub Dups()
Dim a, i As Long, x As Long, rCell As Range
a = Sheets("Sheet1").Columns("A").SpecialCells(2, 2)
With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    For i = 1 To UBound(a, 1)
        If Not .exists(Trim(a(i, 1))) Then
            x = x + 1
            .Item(Trim(a(i, 1))) = x
        End If
    Next
    For Each rCell In Sheets("Sheet1").Columns("B").SpecialCells(2, 2)
        If .exists(Trim(rCell.Value)) Then
            rCell.Offset(, 1) = Trim(rCell.Value)
        End If
    Next
End With
Set rCell = Nothing
End Sub

The error is coming somewhere in the For i = 1 To UBound(a, 1) I believe. If anyone could help me fix this error that would be great.

-J

Re: Macro help find duplicates in two columns

Quote from dangle;605894

J

Does this give you what you need?

Code

Sub Dups()
Dim a, i As Long, x As Long, rCell As Range
a = Sheets("Sheet1").Columns("A").SpecialCells(2, 2)
With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    For i = 1 To UBound(a, 1)
        If Not .exists(Trim(a(i, 1))) Then
            x = x + 1
            .Item(Trim(a(i, 1))) = x
        End If
    Next
    For Each rCell In Sheets("Sheet1").Columns("B").SpecialCells(2, 2)
        If .exists(Trim(rCell.Value)) Then
            rCell.Offset(, 1) = Trim(rCell.Value)
        End If
    Next
End With
Set rCell = Nothing
End Sub

Display More

This code works for the columns A and B, but what if I want to look for duplicates in column D vs. column A, and then output that in column E?

I need a macro that will search two columns and locate any duplicates between the two columns. Here is an example of my data:

[TABLE="width: 895"]

3051CD2A22A1AS1E5M5CNJ3-1199WDAB3AFFW71DC00

3051SFPDS010W3M0800D31AA1A2KDM5C5

3051CG4A04A1AH3E5CN

475HP1EKLUGMT

03031-0313-0013

3051CD2A03A1AH2E5CN

475HP1EKLUGMT

3144PD1A1E5M5Q4CNB4

[/TABLE]

The first column is column "A". The second column is column "B". In column "C", I want the output to be 475HP1EKLUGMT in the cell 2. The macro needs to run until the last used cell in each column has been checked for duplicates.

Thanks,
J

I need help finding only consecutive duplicates based on criteria in two columns. The first column, A, is the one with all the 1's and the 3 at the end. Column B is the long series of numbers and letters starting with 3:
A B
[TABLE="width: 367"]

[TD="align: right"]1[/TD]

3051CD2A22A1AS2M5E5CN

[TD="align: right"]1[/TD]

3051CD2A22A1AS2M5E5CN

[TD="align: right"]1[/TD]

3051CD2A22A1AS9E5M5CN

[TD="align: right"]1[/TD]

3051CD2A22A1AS9E5M5CN

[TD="align: right"]1[/TD]

3051CD2A22A1AS2B4E5M5CN

[TD="align: right"]1
[/TD]

3051TG3A2B21AS1E5M5CNP3

[TD="align: right"]1[/TD]

3051CD2A22A1AS2B4E5CN

[TD="align: right"]3[/TD]

3051CD2A22A1AS2B4E5CN

[/TABLE]

I need a macro that checks if there are consecutive duplicate entries in both column A and B. The two bold lines are examples of the consecutive duplicates I am talking about. The last two lines are not duplicate because the numbers in column A are not the same. If there are, then it will say "YES" in a new column in both of the rows that have the consecutive entries. And it needs to be able to skip the blank rows. Any help would be much appreciated.

-J

I have this macro that I am using to find duplicates using two criteria and put a YES in the column if they meet that criteria. I need a way so that the macro will be applied to all the used cells.

Sub Find_Duplicates()
'
' Test2 Macro
'

'
Range("AE1").Select
Selection.Copy
Range("AF1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "DUPLICATE_ITEM?"
Range("AF2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-23]=RC[-12],""YES"","""")"
Range("AF2").Select
Selection.AutoFill Destination:=Range("AF2:AF1646"), Type:=xlFillDefault
Range("AF2:AF1646").Select

End Sub

Right now my range only goes from AF2:AF1646, but for other sheets that range will not always be the same. How do I input a range that will apply the macro to entire column?

Thanks,
Josh

http://www.mrexcel.com/forum/s…php?p=3112521#post3112521

Re: Macro to Unscramble Duplicate Values and Count them in Pivot Table

[TABLE="class: cms_table"]

[TD="class: cms_table_xl63, bgcolor: transparent"]3051CD2A02A1AHBE5M5J3CNT1
[/TD]
[TD="class: cms_table_xl64, width: 35, bgcolor: transparent, align: right"]2
[/TD]

[TD="class: cms_table_xl65, bgcolor: yellow"]3051CD2A02A1AS5E5M5CN
[/TD]
[TD="class: cms_table_xl66, bgcolor: yellow, align: right"]2
[/TD]

[/TABLE]

This is my desired result... In this situation the S5 would come before the M5, but the numbers and letters will not always be like this. In other situations other letter and number combinations may need to preceed others. I just need a way to recognize if a string has the exact same letters and numbers as another string, no matter what order those letters and numbers are in.

[HR][/HR] [TABLE="width: 308"]

[TD="class: xl63, width: 376, bgcolor: transparent"]3051CD2A02A1AHBE5M5J3CNT1[/TD]
[TD="class: xl64, width: 35, bgcolor: transparent, align: right"]2[/TD]

[TD="class: xl65, bgcolor: yellow"]3051CD2A02A1AM5E5S5CN[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]1[/TD]

[TD="class: xl65, bgcolor: yellow"]3051CD2A02A1AS5E5M5CN[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]1[/TD]

[/TABLE]

I need help making a Macro that finds matching data in a column and counts them. In the pivot table shown above, the highlighted numbers should be the exact same, but a couple of numbers and letters are switched around, so they are counted as 1 each instead of having one line of 2. Any help with finding a working Macro for this would be appreciated.