Help writing a formula
 

I assume that your Title starts from C1 cell and AK starts from D1 cell.

Copy and paste the below formula in A1 cell
=IF(OR($C1="",$D1=""),"",TRIM($D1)&"-"&RIGHT(TRIM($C1),1))

Copy and paste the below formula in B1 cell
=IF(OR($C1="",$D1=""),"",TRIM($D1)&"-"&RIGHT(TRIM($C1),1)&"-"&SUMPRODUCT(($C$1:$C1=$C1)*($D$1:$D1=$D1)))

Drag the A1 & B1 cell formula to the remaining cells of A & B Column based
on the C & D Column data.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"WA" wrote:

I have a huge database that I need to create numbering for each line like
this example: AK-233 (2 letter state, a dash, and 1-4 digit number), but the
number doesn't change each line - sometimes the number will be duplicated on
multiple lines because those lines have the same title, I also need to create
another cell extending that letter/number combo that will differentiate the
1st cell.
So the first cell stays the same if the title is the same. The second cell
adds another layer of numbering to differentiate the first cell.
See example:
(Row1, Col 1-4) AK-1, AK-1-1, Title1, AK
(Row2, Col 1-4) AK-1, AK-1-2, Title1, AK
(Row3, Col 1-4) AK-1, AK-1-3, Title1, AK
(Row4, Col 1-4) AK-2, AK-2-1, Title2, AK
(Row5, Col 1-4) AK-2, AK-2-2, Title2, AK
So - I have the Title and the State (2 letters) in different cells (cell 3 &
cell 4 in each row above). Now I need a formula for cell 1 and cell 2.
Cell 1: Copies the state letters from cell 4, adds a dash, adds a number
starting with 1. IF the title is the same as the one in the row above it -
the number does not change. IF the title is different than the one in the row
above it - than the number should add +1 to the previous number.
Cell 2: This copies cell 1 but adds +1 to the cell above it if it is the
same number.

I've really confused myself with how to do this - or explain it. Anyone out
there that may be willing to help get me started on this?
Thanks!
Wendy
--
WA

Help writing a formula
 

correction to formula below.
--
WA


"WA" wrote:

Hi! This get's me close - let me show a better example so you can see what I
want and what your formula gave me:
Here's what I want in column A & B. I have columns C & D:
AK-1 AK-1-1 Anchorage Golf Course AK
AK-1 AK-1-1 Anchorage Golf Course AK
AK-1 AK-1-1 Anchorage Golf Course AK
AK-2 AK-2-1 Birch Ridge Golf Course AK
AK-2 AK-2-1 Birch Ridge Golf Course AK
AK-3 AK-3-1 Chena Bend Golf Course AK
AK-3 AK-3-1 Chena Bend Golf Course AK
AK-3 AK-3-1 Chena Bend Golf Course AK
CA-1 CA-1-1 Woods Valley GC CA
CA-1 CA-1-1 Woods Valley GC CA
CA-2 CA-2-1 Yolo Fliers Club CA
CA-2 CA-2-1 Yolo Fliers Club CA
CA-2 CA-2-1 Yolo Fliers Club CA
CA-3 CA-3-1 Yorba Linda Country Club CA
CA-3 CA-3-1 Yorba Linda Country Club CA

Here's what your formula gave me:
AK-e AK-e-1 Anchorage Golf Course AK
AK-e AK-e-2 Anchorage Golf Course AK
AK-e AK-e-3 Anchorage Golf Course AK
AK-e AK-e-1 Birch Ridge Golf Course AK
AK-e AK-e-2 Birch Ridge Golf Course AK
AK-e AK-e-1 Chena Bend Golf Course AK
AK-e AK-e-2 Chena Bend Golf Course AK
AK-e AK-e-3 Chena Bend Golf Course AK
CA-C CA-C-1 Woods Valley GC CA
CA-C CA-C-2 Woods Valley GC CA
CA-b CA-b-1 Yolo Fliers Club CA
CA-b CA-b-2 Yolo Fliers Club CA
CA-b CA-b-3 Yolo Fliers Club CA
CA-b CA-b-1 Yorba Linda Country Club CA
CA-b CA-b-2 Yorba Linda Country Club CA

Instead of a number in the formula is picking up the last letter in column
C. I'm not sure how to fix that part . . . you could also see the answer
above yours as I was able to create Column B using this:
'=A1&"-"&TEXT(1,"#"), but I still can't get column A to change as column C
changes. Additional ideas?
--
WA


"Ms-Exl-Learner" wrote:

I assume that your Title starts from C1 cell and AK starts from D1 cell.

Copy and paste the below formula in A1 cell
=IF(OR($C1="",$D1=""),"",TRIM($D1)&"-"&RIGHT(TRIM($C1),1))

Copy and paste the below formula in B1 cell
=IF(OR($C1="",$D1=""),"",TRIM($D1)&"-"&RIGHT(TRIM($C1),1)&"-"&SUMPRODUCT(($C$1:$C1=$C1)*($D$1:$D1=$D1)))

Drag the A1 & B1 cell formula to the remaining cells of A & B Column based
on the C & D Column data.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"WA" wrote:

I have a huge database that I need to create numbering for each line like
this example: AK-233 (2 letter state, a dash, and 1-4 digit number), but the
number doesn't change each line - sometimes the number will be duplicated on
multiple lines because those lines have the same title, I also need to create
another cell extending that letter/number combo that will differentiate the
1st cell.
So the first cell stays the same if the title is the same. The second cell
adds another layer of numbering to differentiate the first cell.
See example:
(Row1, Col 1-4) AK-1, AK-1-1, Title1, AK
(Row2, Col 1-4) AK-1, AK-1-2, Title1, AK
(Row3, Col 1-4) AK-1, AK-1-3, Title1, AK
(Row4, Col 1-4) AK-2, AK-2-1, Title2, AK
(Row5, Col 1-4) AK-2, AK-2-2, Title2, AK
So - I have the Title and the State (2 letters) in different cells (cell 3 &
cell 4 in each row above). Now I need a formula for cell 1 and cell 2.
Cell 1: Copies the state letters from cell 4, adds a dash, adds a number
starting with 1. IF the title is the same as the one in the row above it -
the number does not change. IF the title is different than the one in the row
above it - than the number should add +1 to the previous number.
Cell 2: This copies cell 1 but adds +1 to the cell above it if it is the
same number.

I've really confused myself with how to do this - or explain it. Anyone out
there that may be willing to help get me started on this?
Thanks!
Wendy
--
WA

Help writing a formula
 

Hi! This get's me close - let me show a better example so you can see what I
want and what your formula gave me:
Here's what I want in column A & B. I have columns C & D:
AK-1 AK-1-1 Anchorage Golf Course AK
AK-1 AK-1-1 Anchorage Golf Course AK
AK-1 AK-1-1 Anchorage Golf Course AK
AK-2 AK-2-1 Birch Ridge Golf Course AK
AK-2 AK-2-1 Birch Ridge Golf Course AK
AK-3 AK-3-1 Chena Bend Golf Course AK
AK-3 AK-3-1 Chena Bend Golf Course AK
AK-3 AK-3-1 Chena Bend Golf Course AK
CA-1 CA-1-1 Woods Valley GC CA
CA-1 CA-1-1 Woods Valley GC CA
CA-2 CA-2-1 Yolo Fliers Club CA
CA-2 CA-2-1 Yolo Fliers Club CA
CA-2 CA-2-1 Yolo Fliers Club CA
CA-3 CA-3-1 Yorba Linda Country Club CA
CA-3 CA-3-1 Yorba Linda Country Club CA

Here's what your formula gave me:
AK-e AK-e-1 Anchorage Golf Course AK
AK-e AK-e-2 Anchorage Golf Course AK
AK-e AK-e-3 Anchorage Golf Course AK
AK-e AK-e-1 Birch Ridge Golf Course AK
AK-e AK-e-2 Birch Ridge Golf Course AK
AK-e AK-e-1 Chena Bend Golf Course AK
AK-e AK-e-2 Chena Bend Golf Course AK
AK-e AK-e-3 Chena Bend Golf Course AK
CA-C CA-C-1 Woods Valley GC CA
CA-C CA-C-2 Woods Valley GC CA
CA-b CA-b-1 Yolo Fliers Club CA
CA-b CA-b-2 Yolo Fliers Club CA
CA-b CA-b-3 Yolo Fliers Club CA
CA-b CA-b-1 Yorba Linda Country Club CA
CA-b CA-b-2 Yorba Linda Country Club CA

Instead of a number in the formula is picking up the last letter in column
C. I'm not sure how to fix that part . . . you could also see the answer
above yours as I was able to create Column B using this:
'=A18&"-"&TEXT(1,"#"), but I still can't get column A to change as column C
changes. Additional ideas?
--
WA


"Ms-Exl-Learner" wrote:

I assume that your Title starts from C1 cell and AK starts from D1 cell.

Copy and paste the below formula in A1 cell
=IF(OR($C1="",$D1=""),"",TRIM($D1)&"-"&RIGHT(TRIM($C1),1))

Copy and paste the below formula in B1 cell
=IF(OR($C1="",$D1=""),"",TRIM($D1)&"-"&RIGHT(TRIM($C1),1)&"-"&SUMPRODUCT(($C$1:$C1=$C1)*($D$1:$D1=$D1)))

Drag the A1 & B1 cell formula to the remaining cells of A & B Column based
on the C & D Column data.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"WA" wrote:

I have a huge database that I need to create numbering for each line like
this example: AK-233 (2 letter state, a dash, and 1-4 digit number), but the
number doesn't change each line - sometimes the number will be duplicated on
multiple lines because those lines have the same title, I also need to create
another cell extending that letter/number combo that will differentiate the
1st cell.
So the first cell stays the same if the title is the same. The second cell
adds another layer of numbering to differentiate the first cell.
See example:
(Row1, Col 1-4) AK-1, AK-1-1, Title1, AK
(Row2, Col 1-4) AK-1, AK-1-2, Title1, AK
(Row3, Col 1-4) AK-1, AK-1-3, Title1, AK
(Row4, Col 1-4) AK-2, AK-2-1, Title2, AK
(Row5, Col 1-4) AK-2, AK-2-2, Title2, AK
So - I have the Title and the State (2 letters) in different cells (cell 3 &
cell 4 in each row above). Now I need a formula for cell 1 and cell 2.
Cell 1: Copies the state letters from cell 4, adds a dash, adds a number
starting with 1. IF the title is the same as the one in the row above it -
the number does not change. IF the title is different than the one in the row
above it - than the number should add +1 to the previous number.
Cell 2: This copies cell 1 but adds +1 to the cell above it if it is the
same number.

I've really confused myself with how to do this - or explain it. Anyone out
there that may be willing to help get me started on this?
Thanks!
Wendy
--
WA