adding a,b,c,etc to end of item in call
 

I have two columns. Col A is "acct nos" where some acct nos are repeated 2,3
or 4 times and some only twice. The second column is random file names, such
as 0001325.jpg. I would like to be able to to rename the 2nd column with the
1st column acct no name and add an a,b,c, etc(however many times the account
no is listed). my "new" name would be something like 0100252401a.jpg,
0100252401b.jpg, etc.
Thanks

adding a,b,c,etc to end of item in call
 

If you can always know that you want to sum every third column of a range, then
I bet some variation of Ron's suggestion will work (depending if you're starting
with the 1st, 2nd, 3rd column in the range).

But if you're inserting a single column and want to include that with your sum,
I'd do something like this:

Insert a new Row to be used as an indicator row--should this column be included
in the sum. Put Y if you want to include it in the sum--anything else and it'll
be skipped.

Then you can use a formula like:

=SUMPRODUCT(--($A$1:$J$1="y"),A2:J2)

I only went from A:J, though. And it sums the numbers in row 2 that have that Y
in row 1 of the associated column. If you insert a new column, just remember to
adjust that new column's indicator cell.

I'd use a helper column (say column C) and put this formula in C1 and drag down:

=LEFT(B1,LEN(B1)-4)&CHAR(COUNTIF($A$1:$A1,$A1)+64)&right(b1,4)
(for upper case letters)

=LEFT(B1,LEN(B1)-4)&CHAR(COUNTIF($A$1:$A1,$A1)+96)&RIGHT(B1,4)
(for lower case letters)

This will fail when you get to the 27th repeat.


RHD3 wrote:

I have two columns. Col A is "acct nos" where some acct nos are repeated 2,3
or 4 times and some only twice. The second column is random file names, such
as 0001325.jpg. I would like to be able to to rename the 2nd column with the
1st column acct no name and add an a,b,c, etc(however many times the account
no is listed). my "new" name would be something like 0100252401a.jpg,
0100252401b.jpg, etc.
Thanks

--

Dave Peterson

adding a,b,c,etc to end of item in call
 

in column C assuming data starts in row 1:

=IF(COUNTIF($A1:$A$100,$A1)0,$A1 &
CHAR(97+COUNTIF($A$1:$A$100,$A1)-(COUNTIF($A1:$A$100,$A1))) &".jpg","")

and copy down

Copy/paste special=values when complete.

HTH

"RHD3" wrote:

I have two columns. Col A is "acct nos" where some acct nos are repeated 2,3
or 4 times and some only twice. The second column is random file names, such
as 0001325.jpg. I would like to be able to to rename the 2nd column with the
1st column acct no name and add an a,b,c, etc(however many times the account
no is listed). my "new" name would be something like 0100252401a.jpg,
0100252401b.jpg, etc.
Thanks

adding a,b,c,etc to end of item in call
 

There were replies to two different posts in that message.

I'd use a helper column (say column C) and put this formula in C1 and drag down:

=LEFT(B1,LEN(B1)-4)&CHAR(COUNTIF($A$1:$A1,$A1)+64)&right(b1,4)
(for upper case letters)

=LEFT(B1,LEN(B1)-4)&CHAR(COUNTIF($A$1:$A1,$A1)+96)&RIGHT(B1,4)
(for lower case letters)

This will fail when you get to the 27th repeat.

Dave Peterson wrote:
<<snipped
RHD3 wrote:

I have two columns. Col A is "acct nos" where some acct nos are repeated 2,3
or 4 times and some only twice. The second column is random file names, such
as 0001325.jpg. I would like to be able to to rename the 2nd column with the
1st column acct no name and add an a,b,c, etc(however many times the account
no is listed). my "new" name would be something like 0100252401a.jpg,
0100252401b.jpg, etc.
Thanks

--

Dave Peterson

--

Dave Peterson

adding a,b,c,etc to end of item in call
 

Sort column A in ascending order. Insert column B ...use formula in row 2:
IF A2=A1,1,"")...in row 3 use IF(A3=A2,B2+1,"")...repeat row 3 formula to
the bottom of your data. Insert column C...use formula
=concatenate(a1,"-",b1). This will make each of your account numbers unique.
You can work further to substitute your added numbers for text...1=a, 2=b,
etc. using vlookup. Don't forget to turn your formulated numbers into values
(copy, paste special, values option).

"Dave Peterson" wrote:

If you can always know that you want to sum every third column of a range, then
I bet some variation of Ron's suggestion will work (depending if you're starting
with the 1st, 2nd, 3rd column in the range).

But if you're inserting a single column and want to include that with your sum,
I'd do something like this:

Insert a new Row to be used as an indicator row--should this column be included
in the sum. Put Y if you want to include it in the sum--anything else and it'll
be skipped.

Then you can use a formula like:

=SUMPRODUCT(--($A$1:$J$1="y"),A2:J2)

I only went from A:J, though. And it sums the numbers in row 2 that have that Y
in row 1 of the associated column. If you insert a new column, just remember to
adjust that new column's indicator cell.

I'd use a helper column (say column C) and put this formula in C1 and drag down:

=LEFT(B1,LEN(B1)-4)&CHAR(COUNTIF($A$1:$A1,$A1)+64)&right(b1,4)
(for upper case letters)

=LEFT(B1,LEN(B1)-4)&CHAR(COUNTIF($A$1:$A1,$A1)+96)&RIGHT(B1,4)
(for lower case letters)

This will fail when you get to the 27th repeat.


RHD3 wrote:

I have two columns. Col A is "acct nos" where some acct nos are repeated 2,3
or 4 times and some only twice. The second column is random file names, such
as 0001325.jpg. I would like to be able to to rename the 2nd column with the
1st column acct no name and add an a,b,c, etc(however many times the account
no is listed). my "new" name would be something like 0100252401a.jpg,
0100252401b.jpg, etc.
Thanks

--

Dave Peterson