# Posts by jpedges38

• ## Finding Specific Letter and Number Strings

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

• ## Finding Specific Letter and Number Strings

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

• ## Macro Sort Alphanumeric Row by Row Alphabetically

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?

• ## Macro Sort Alphanumeric Row by Row Alphabetically

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

• ## Change Report Filter Multiple Pivot Tables

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

• ## Macro Help: Finding Letter/Number Combos

Re: Macro Help: Finding Letter/Number Combos

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

• ## Macro Help: Finding Letter/Number Combos

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?

• ## Macro Help: Finding Letter/Number Combos

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

[tr]

[td][/td]

[/tr]

[tr]

[td]

3051CD2A02A1AH2B9E5L4

[/td]

[/tr]

[tr]

[td]

3051CD2A02A1AH2B9E5L4CN

[/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td]

3051CG4A02A1AH2B9E5

[/td]

[/tr]

[tr]

[td]

3051CG4A02A1AH2B9E5CN

[/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td]

3051CD3A03A1AH3B3M5E5T1

[/td]

[/tr]

[tr]

[td]

3051CD3A03A1AH3B3M5E5T1CN

[/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td]

3144PD1A1E1B4M5CN

[/td]

[/tr]

[tr]

[td]

3144PD1A1E1B4M5

[/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td]

3051CG5A02A1AH2E5M5CN

[/td]

[/tr]

[tr]

[td]

3051CG5A02A1AH2E5M5

[/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td]

3051CG5A02A1AH2E5M5CNQ4

[/td]

[/tr]

[tr]

[td]

3051CG5A02A1AH2E5M5Q4CN

[/td]

[/tr]

[/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"]

[tr]

[td]

3051CG5A02A1AH2E5M5CNQ4>3051CG5A02A1AH2E5M5Q4>3051CG5A02A1AH2E5M5Q4CN

[/td]

[td][/td]

[/tr]

[/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

• ## Find Last Cell in Row

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.

Code
Range("B2").End(xlLeft).Column.FormulaR1C1 = "=IF(R[2]C[2]=""Grand Total"","""",IF(R[3]C[2]="""",""QUOTES"",""ORDERS""))"
• ## Find Minimum Year In a Column

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"]

[tr]

[td]

QUOTE YEARS

[/td]

[td][/td]

[/tr]

[tr]

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

[td][/td]

[/tr]

[tr]

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

[/tr]

[tr]

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

[/tr]

[tr]

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

[/tr]

[tr]

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

[/tr]

[tr]

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

[/tr]

[tr]

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

[td][/td]

[/tr]

[tr]

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

[/tr]

[tr]

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

[/tr]

[tr]

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

[/tr]

[/TABLE]

• ## Macro Help: Find Characters in a String

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"]

[tr]

[td]

COLUMN Q

[/td]

[/tr]

[tr]

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

[/tr]

[tr]

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

[/tr]

[tr]

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

[/tr]

[tr]

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

[/tr]

[tr]

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

[/tr]

[tr]

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

[/tr]

[tr]

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

[/tr]

[tr]

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

[/tr]

[tr]

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

[/tr]

[tr]

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

[/tr]

[tr]

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

[/tr]

[tr]

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

[/tr]

[tr]

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

[/tr]

[tr]

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

[/tr]

[/TABLE]

• ## Macro Help If/Then Statements

I currently have a macro that follows:

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

• ## Macro Run Time Error 13: Mismatch

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:

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

• ## Macro help find duplicates in two columns

Re: Macro help find duplicates in two columns

Quote from dangle;605894

J

Does this give you what you need?

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?

• ## Macro help find duplicates in two columns

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"]

[tr]

[td]

3051CD2A22A1AS1E5M5CNJ3-1199WDAB3AFFW71DC00

[/td]

[td]

3051SFPDS010W3M0800D31AA1A2KDM5C5

[/td]

[/tr]

[tr]

[td]

3051CG4A04A1AH3E5CN

[/td]

[td]

475HP1EKLUGMT

[/td]

[/tr]

[tr]

[td]

03031-0313-0013

[/td]

[td]

3051CD2A03A1AH2E5CN

[/td]

[/tr]

[tr]

[td]

475HP1EKLUGMT

[/td]

[td]

3144PD1A1E5M5Q4CNB4

[/td]

[/tr]

[/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

• ## Macro Find Only Consecutive Duplicates in a Column

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"]

[tr]

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

[td]

3051CD2A22A1AS2M5E5CN

[/td]

[/tr]

[tr]

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

[td]

3051CD2A22A1AS2M5E5CN

[/td]

[/tr]

[tr]

[td][/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td][/td]

[/tr]

[tr]

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

[td]

3051CD2A22A1AS9E5M5CN

[/td]

[/tr]

[tr]

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

[td]

3051CD2A22A1AS9E5M5CN

[/td]

[/tr]

[tr]

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

[td]

3051CD2A22A1AS2B4E5M5CN

[/td]

[/tr]

[tr]

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

[td]

3051TG3A2B21AS1E5M5CNP3

[/td]

[/tr]

[tr]

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

[td]

3051CD2A22A1AS2B4E5CN

[/td]

[/tr]

[tr]

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

[td]

3051CD2A22A1AS2B4E5CN

[/td]

[/tr]

[/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

• ## Macro Range Help...

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

• ## Macro to Unscramble Duplicate Values and Count them in Pivot Table

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

[TABLE="class: cms_table"]

[tr]

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

[/tr]

[tr]

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

[/tr]

[/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.

• ## Macro to Unscramble Duplicate Values and Count them in Pivot Table

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

[tr]

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

[/tr]

[tr]

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

[/tr]

[tr]

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

[/tr]

[/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.