vlookup by date

[PointerMan]

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.

 

[JBeaucaire]

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.

 

[PointerMan]

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]

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,""))

 

[JBeaucaire]

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?

 

[JBeaucaire]

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?

 

[PointerMan]

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]

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

 

[JBeaucaire]

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

 

[JBeaucaire]

I uploaded the file to here if you would prefer to get it publicly:

http://www.sendspace.com/file/hcuf6x

 

[Roger Govier]

Hi

Copy the data from each column to a new Sheet, pasting each block below the
first one in column A
Select column A>Data>Text to columns>Fixed Width>Next>Next>Date>M/d/y>Finish

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