A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Help writing a formula



 
 
Thread Tools Display Modes
  #1  
Old May 27th, 2010, 10:12 PM posted to microsoft.public.excel.misc
WA
external usenet poster
 
Posts: 35
Default Help writing a formula

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
  #2  
Old May 27th, 2010, 11:00 PM posted to microsoft.public.excel.misc
WA
external usenet poster
 
Posts: 35
Default Help writing a formula

SORRY that formula at the bottom for column B that works is:
'=A1&"-"&TEXT(1,"#") NOT A18! (typo)
--
WA


"WA" wrote:

This one helped me create one of the columns I need (B), but I still can't
get column A to work. Here's a better example of what I need below. I have
Column C & D filled, I need to create column A where I can differentiate when
column C changes (see all Anchorage GCs are 1 and Birch GC are 2, etc.:
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

This is what I used for column B from your notes: '=A1&"-"&TEXT(1,"#"). This
is perfect. How do I get column A to change to the next number when column C
is different?
--
WA


"ck13" wrote:

Hi, I dun really understand what is your end product but just give it a try
and maybe you can make something out of it. I assume that you have
information in cell 3 and 4. What you want to work at is cell 1 and 2.

Start from cell A1 =D1&"-"&TEXT(1,"#")
Cell B1 =A1&"-"&TEXT(1,"#")

In cell A2 and B2, it will be different from above.

In A2 =IF(C2=C1,+D2&"-"&TEXT(MID(A1,4,1),"#"),D2&"-"&TEXT(MID(A1,4,1),"#")+1)

In B2
=IF(MID(A2,4,1)=MID(A1,4,1),A1&"-"&TEXT(MID(B1,6,1),"#")+1,A2&"-"&TEXT(1,"#"))


Drag the formula in cell A2 and B2 down. Change the range as you require.

Let me know if it helps you.


"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

  #3  
Old May 27th, 2010, 11:04 PM posted to microsoft.public.excel.misc
WA
external usenet poster
 
Posts: 35
Default Help writing a formula

This reply was supposed to be for MS-Exl-Learner - I replied to the wrong one!
--
WA


"WA" wrote:

SORRY that formula at the bottom for column B that works is:
'=A1&"-"&TEXT(1,"#") NOT A18! (typo)
--
WA


"WA" wrote:

This one helped me create one of the columns I need (B), but I still can't
get column A to work. Here's a better example of what I need below. I have
Column C & D filled, I need to create column A where I can differentiate when
column C changes (see all Anchorage GCs are 1 and Birch GC are 2, etc.:
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

This is what I used for column B from your notes: '=A1&"-"&TEXT(1,"#"). This
is perfect. How do I get column A to change to the next number when column C
is different?
--
WA


"ck13" wrote:

Hi, I dun really understand what is your end product but just give it a try
and maybe you can make something out of it. I assume that you have
information in cell 3 and 4. What you want to work at is cell 1 and 2.

Start from cell A1 =D1&"-"&TEXT(1,"#")
Cell B1 =A1&"-"&TEXT(1,"#")

In cell A2 and B2, it will be different from above.

In A2 =IF(C2=C1,+D2&"-"&TEXT(MID(A1,4,1),"#"),D2&"-"&TEXT(MID(A1,4,1),"#")+1)

In B2
=IF(MID(A2,4,1)=MID(A1,4,1),A1&"-"&TEXT(MID(B1,6,1),"#")+1,A2&"-"&TEXT(1,"#"))


Drag the formula in cell A2 and B2 down. Change the range as you require.

Let me know if it helps you.


"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

  #4  
Old May 28th, 2010, 03:59 AM posted to microsoft.public.excel.misc
Ms-Exl-Learner
external usenet poster
 
Posts: 522
Default Help writing a formula

Insert a Blank Line or Keep the Column Title in the 1st Row and make sure
that your data should start from 2nd Row.

Once again repeating you, that the data should start from 2nd Row.

I am using the E Column as helper Column.

Copy and paste the below formula in E2 cell.
=IF(D2="","",IF($D2="","",IF(AND(ROW()=2,$D2""), 1,IF(SUMIF($D$1:$D1,$D2,$E$1:$E1)=1,MAX(IF(($D$1: $D1=$D2),($E$1:$E1))),IF(SUMIF($D$1:$D1,$D2,$E$1:$ E1)=0,MAX($E$1:$E1)+1)))))
(Array Formula)

After pasting the formula in E2 cell place the cursor in E2 cell and press
F2 and hit CNTRL+SHIFT+ENTER. Since it is an array formula so it requires
CNTRL+SHIFT+ENTER and the general enter wont work. After hitting the
Cntrl+Shift+Enter you can notice that the formula will be displayed in the
formula bar by covered with Curly Braces{}.
Like the below:-
{=IF(D2="","",IF($D2="","",IF(AND(ROW()=2,$D2"") ,1,IF(SUMIF($D$1:$D1,$D2,$E$1:$E1)=1,MAX(IF(($D$1 :$D1=$D2),($E$1:$E1))),IF(SUMIF($D$1:$D1,$D2,$E$1: $E1)=0,MAX($E$1:$E1)+1)))))}
Don’t add the Curly Braces manually.
Drag the E2 cell to the remaining cells of E column based on the C & D
Column Data

Copy and paste the below formula in A2 cell.
=IF(OR($C2="",$D2=""),"",TRIM($D2)&"-"&E2)
Drag the A2 cell to the remaining cells of A column based on the C & D
Column Data

Copy and paste the below formula in B2 cell.
=IF(OR($C2="",$D2=""),"",TRIM($D2)&"-"&E2&"-"&SUMPRODUCT(($C$2:$C2=$C2)*($D$2:$D2=$D2)))
Drag the B2 cell to the remaining cells of B column based on the C & D
Column Data

Your Data and result will be like this.

Row/Col A-Col B-Col C-Col D-Col E-Col
Row-1 1st Count 2nd Count Title Heading Helper Col
Row-2 AK-1 AK-1-1 Anchorage Golf Course AK 1
Row-3 AK-1 AK-1-2 Anchorage Golf Course AK 1
Row-4 AK-1 AK-1-3 Anchorage Golf Course AK 1
Row-5 AK-1 AK-1-1 Birch Ridge Golf Course AK 1
Row-6 AK-1 AK-1-2 Birch Ridge Golf Course AK 1
Row-7 AK-1 AK-1-1 Chena Bend Golf Course AK 1
Row-8 AK-1 AK-1-2 Chena Bend Golf Course AK 1
Row-9 AK-1 AK-1-3 Chena Bend Golf Course AK 1
Row-10 CA-2 CA-2-1 Woods Valley GC CA 2
Row-11 CA-2 CA-2-2 Woods Valley GC CA 2
Row-12 CA-2 CA-2-1 Yolo Fliers Club CA 2
Row-13 CA-2 CA-2-2 Yolo Fliers Club CA 2
Row-14 CA-2 CA-2-3 Yolo Fliers Club CA 2
Row-15 CA-2 CA-2-1 Yorba Linda Country Club CA 2
Row-16 CA-2 CA-2-2 Yorba Linda Country Club CA 2
Row-17 AK-1 AK-1-4 Anchorage Golf Course AK 1

Hope it's Clear.

Remember to Click Yes, if this post helps!

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


"WA" wrote:

I AM SO CLOSE! :-)
Both ck13 & Ms-Exl-Learner have helped me down the path but I have 1 piece
missing.
Heres what I have now (Col A - done, Col B - not done):
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-4 CA-4-1 Woods Valley GC CA
CA-4 CA-4-1 Woods Valley GC CA
CA-5 CA-5-1 Yolo Fliers Club CA
CA-5 CA-5-1 Yolo Fliers Club CA
CA-5 CA-5-1 Yolo Fliers Club CA
CA-6 CA-6-1 Yorba Linda Country Club CA
CA-6 CA-6-1 Yorba Linda Country Club CA

Col A Cell 1, I typed in what I wanted: AK-1 (no formula), then in A2 I used:
=IF(C2=C1,+D2&"-"&TEXT(MID(A1,4,1),"#"),D2&"-"&TEXT(MID(A1,4,1),"#")+1)
When I drag it down Col A is perfect.

ColB Cell 1, I used:
=A1&"-"&TEXT(1,"#")
but I don't know how to change the last number so it continues to 'count' if
the course has the same name. I want Col B to look like this:

AK-1-1
AK-1-2
AK-1-3
AK-2-1
AK-2-2
AK-3-1
AK-3-2
AK-3-3
CA-1-1
CA-1-2
CA-2-1
CA-2-2
CA-2-3
CA-3-1
CA-3-2

Thank you for helping!
--
WA


"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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:29 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.