Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup by date
I have about 30 columns of data that start with a date in each of them. I
want to pull all of the January (February, etc) dates from each of the columns into one column in another worksheet. My second column will have titles January, February, March, etc and will be filled with the appropriate data from the first sheet. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup by date
I'd look at SUMIF. If the dates are in A and the values to be summed are in
B, try something like this: =SUM(IF(MONTH($A$1:$A$19)=1,$B$1:$B$19,"")) ....confirmed by pressing CTRL-SHIFT-ENTER. If you only press enter, you'll get the wrong sum. This is an array formula and the CSE will result in a braces { } appearing around your formula. Now, change the 1 to 2 in the formula to get month 2, or February...and so on. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "PointerMan" wrote: I have about 30 columns of data that start with a date in each of them. I want to pull all of the January (February, etc) dates from each of the columns into one column in another worksheet. My second column will have titles January, February, March, etc and will be filled with the appropriate data from the first sheet. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup by date
This is the data for 4 cells. Each row below represents one cell. I'm
looking for the cell data based on the first 3 digits of the cell. 12-17-08 SHIP 70302-22101-112 37 12-18-08 PKG 70302-22101-111 28 12-30-08 SHIP 74A350834-2019 37 12-30-08 SHIP 901-069-113-134 3 "JBeaucaire" wrote: I'd look at SUMIF. If the dates are in A and the values to be summed are in B, try something like this: =SUM(IF(MONTH($A$1:$A$19)=1,$B$1:$B$19,"")) ...confirmed by pressing CTRL-SHIFT-ENTER. If you only press enter, you'll get the wrong sum. This is an array formula and the CSE will result in a braces { } appearing around your formula. Now, change the 1 to 2 in the formula to get month 2, or February...and so on. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "PointerMan" wrote: I have about 30 columns of data that start with a date in each of them. I want to pull all of the January (February, etc) dates from each of the columns into one column in another worksheet. My second column will have titles January, February, March, etc and will be filled with the appropriate data from the first sheet. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup by date
You can use an almost identical formula to get a "count" of the items in the
range as well: =COUNT(IF(MONTH($A$1:$A$19)=1,$A$1:$A$19,"")) -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "JBeaucaire" wrote: I'd look at SUMIF. If the dates are in A and the values to be summed are in B, try something like this: =SUM(IF(MONTH($A$1:$A$19)=1,$B$1:$B$19,"")) ...confirmed by pressing CTRL-SHIFT-ENTER. If you only press enter, you'll get the wrong sum. This is an array formula and the CSE will result in a braces { } appearing around your formula. Now, change the 1 to 2 in the formula to get month 2, or February...and so on. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "PointerMan" wrote: I have about 30 columns of data that start with a date in each of them. I want to pull all of the January (February, etc) dates from each of the columns into one column in another worksheet. My second column will have titles January, February, March, etc and will be filled with the appropriate data from the first sheet. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup by date
Well, guessing a little here. Are you manually deciding on the 3-digits to
look for? From your dataset I chose 703. Since the cells with that data in it also have dashes, I think excel believes that data to be text, so I put "703" in the formula to treat it as text, too. This is also a CSE formula (Ctrl-Shift-Enter), and result in 65 as the answer: =SUMPRODUCT(--(MONTH($A$1:$A$4)=12), --(LEFT($C$1:$C$4,3)="703"),$D$1:$D$4) Is this closer? -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "PointerMan" wrote: This is the data for 4 cells. Each row below represents one cell. I'm looking for the cell data based on the first 3 digits of the cell. 12-17-08 SHIP 70302-22101-112 37 12-18-08 PKG 70302-22101-111 28 12-30-08 SHIP 74A350834-2019 37 12-30-08 SHIP 901-069-113-134 3 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup by date
Well, guessing a little here. Are you manually deciding on the 3-digits to
look for? From your dataset I chose 703. Since the cells with that data in it also have dashes, I think excel believes that data to be text, so I put "703" in the formula to treat it as text, too. This is also a CSE formula (Ctrl-Shift-Enter), and result in 65 as the answer: =SUMPRODUCT(--(MONTH($A$1:$A$4)=12), --(LEFT($C$1:$C$4,3)="703"),$D$1:$D$4) Is this closer? -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "PointerMan" wrote: I have about 30 columns of data that start with a date in each of them. I want to pull all of the January (February, etc) dates from each of the columns into one column in another worksheet. My second column will have titles January, February, March, etc and will be filled with the appropriate data from the first sheet. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup by date
I don't need the count of items. I'm trying to get the actual contents of
all of the cells in Sheet1 to be sorted into columns on Sheet2 by the first three digits of the cell. In my data the first three digits are "12-". This would get sorted into a December column on Sheet2. "JBeaucaire" wrote: Well, guessing a little here. Are you manually deciding on the 3-digits to look for? From your dataset I chose 703. Since the cells with that data in it also have dashes, I think excel believes that data to be text, so I put "703" in the formula to treat it as text, too. This is also a CSE formula (Ctrl-Shift-Enter), and result in 65 as the answer: =SUMPRODUCT(--(MONTH($A$1:$A$4)=12), --(LEFT($C$1:$C$4,3)="703"),$D$1:$D$4) Is this closer? -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "PointerMan" wrote: This is the data for 4 cells. Each row below represents one cell. I'm looking for the cell data based on the first 3 digits of the cell. 12-17-08 SHIP 70302-22101-112 37 12-18-08 PKG 70302-22101-111 28 12-30-08 SHIP 74A350834-2019 37 12-30-08 SHIP 901-069-113-134 3 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup by date
This is doable, but it's a pretty complicated array formula and it's
different for each month. I've mocked up a sheet and laid it out, I just need to sent it to you. My email address is jerry at devstudios dot com Drop me a note and I'll send it to you to examine. Jerry Beaucaire -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "JBeaucaire" wrote: Well, guessing a little here. Are you manually deciding on the 3-digits to look for? From your dataset I chose 703. Since the cells with that data in it also have dashes, I think excel believes that data to be text, so I put "703" in the formula to treat it as text, too. This is also a CSE formula (Ctrl-Shift-Enter), and result in 65 as the answer: =SUMPRODUCT(--(MONTH($A$1:$A$4)=12), --(LEFT($C$1:$C$4,3)="703"),$D$1:$D$4) Is this closer? -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "PointerMan" wrote: I have about 30 columns of data that start with a date in each of them. I want to pull all of the January (February, etc) dates from each of the columns into one column in another worksheet. My second column will have titles January, February, March, etc and will be filled with the appropriate data from the first sheet. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup by date
This is doable, but it's a pretty complicated array formula and it's
different for each month. I've mocked up a sheet and laid it out, I just need to sent it to you. My email address is jerry at devstudios dot com Drop me a note and I'll send it to you to examine. Jerry Beaucaire -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "PointerMan" wrote: I don't need the count of items. I'm trying to get the actual contents of all of the cells in Sheet1 to be sorted into columns on Sheet2 by the first three digits of the cell. In my data the first three digits are "12-". This would get sorted into a December column on Sheet2. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup by date
I uploaded the file to here if you would prefer to get it publicly:
http://www.sendspace.com/file/hcuf6x -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "JBeaucaire" wrote: This is doable, but it's a pretty complicated array formula and it's different for each month. I've mocked up a sheet and laid it out, I just need to sent it to you. My email address is jerry at devstudios dot com Drop me a note and I'll send it to you to examine. Jerry Beaucaire -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "JBeaucaire" wrote: Well, guessing a little here. Are you manually deciding on the 3-digits to look for? From your dataset I chose 703. Since the cells with that data in it also have dashes, I think excel believes that data to be text, so I put "703" in the formula to treat it as text, too. This is also a CSE formula (Ctrl-Shift-Enter), and result in 65 as the answer: =SUMPRODUCT(--(MONTH($A$1:$A$4)=12), --(LEFT($C$1:$C$4,3)="703"),$D$1:$D$4) Is this closer? -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "PointerMan" wrote: I have about 30 columns of data that start with a date in each of them. I want to pull all of the January (February, etc) dates from each of the columns into one column in another worksheet. My second column will have titles January, February, March, etc and will be filled with the appropriate data from the first sheet. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup by date
Hi
Copy the data from each column to a new Sheet, pasting each block below the first one in column A Select column ADataText to columnsFixed WidthNextNextDateM/d/yFinish You will now have valid dates in column A and alongside in column B, your data for that day. If you did want the Month, then insert a column at B and in B2 =TEXT(A2,"mmm") Copy down -- Regards Roger Govier "PointerMan" wrote in message ... I have about 30 columns of data that start with a date in each of them. I want to pull all of the January (February, etc) dates from each of the columns into one column in another worksheet. My second column will have titles January, February, March, etc and will be filled with the appropriate data from the first sheet. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP and date issue | Excel Discussion (Misc queries) | |||
vlookup using date | Excel Discussion (Misc queries) | |||
vlookup with date formats | Excel Worksheet Functions | |||
Vlookup and Date | Excel Worksheet Functions | |||
Date VLookup | Excel Worksheet Functions |