Help With Adding a Date Column

[emgo]

I’ve spent a lot of time getting this far, I’m sure you can tell I’m not an experienced Excel user. I used a formula I found here in the forensics files that was close to what I needed and modified it. I need to add a date column that corresponds to the adjacent columns in Sheet2. I think the date column needs to added to "named range" tbl but I can't figure out how to that. I’m sure there are ways to accomplish the other part of what I have done in much cleaner method, feel free to show me a better way. I need to learn. I probably haven’t explain this very well, please ask questions. Take a look at my attached example, maybe it will help.



Thanks,

Ken

 

[Hui]

A11:
=INDEX(OFFSET(tbl,,-16,,1), SMALL(IF(COUNTIFS($C$1, Sheet1!$A$2:$A$2000, $D$1, Sheet1!$B$2:$B$2000), ROW(tbl)-MIN(ROW(tbl))+1), ROW($A2)), 1) Ctrl+Shift+Enter

 

[bosco_yip]

Try this one formula way,

1] Define Name : tbl >>

Refer to : =Sheet1!$G$2:$Y$2000

2] In "Sheet2" A11, array formula copy across to S11 and all copy down :

=IF(A$10="","",IFERROR(INDEX(tbl,SMALL(IF(Sheet1!$A$2:$A$2000=OFFSET($A$1,,INT((COLUMNS($A:A)-1)/5)*5+2),IF(Sheet1!$B$2:$B$2000=OFFSET($A$1,,INT((COLUMNS($A:A)-1)/5)*5+3),ROW(tbl)-MIN(ROW(tbl))+1)),ROWS($11:11)),MATCH(A$10,Sheet1!$G$1:$Y$1,0)),""))

Regards
Bosco

 

[emgo]

Hui,

Your formula worked great and much simpler than what I was trying to do. Thank you!



Bosco,

Your formula also worked great and really cleaned up what I had initially done. I’m going to use it and go through the formula step by step so I can fully understand it.



Thanks to both of you!!



Ken

 

[rahulshewale1]

Hii,

bosco sir truly master of excel.
hii emgo i have use helper column to active result.please see attached sheet.


Thanks
rahul shewale

 

[bosco_yip]

Hii,

bosco sir truly master of excel.
hii emgo i have use helper column to active result.please see attached sheet.


Thanks
rahul shewale

Hi rahul shewale,

Here's my formula way to use helper column for your reference.

1] In "Sheet1", A2 formula copy down :

=B2&C2&COUNTIFS($B$2:B2,B2,$C$2:C2,C2)

2] In "Sheet2", A113 formula copy across and down :

=IFERROR(VLOOKUP($C$1&$D$1&ROWS($1:1),Sheet1!$A$1:$Z$1568,MATCH(A$10,Sheet1!$A$1:$Z$1,0),0),"")

Regards
Bosco

 

[Haseeb A]

Hello Ken,

Here are my thoughts about your file.

Create a unique list of your vehicles & tire type. Then select Vehicle & tire type, see results related to the selection.

If you are displaying results for multiple columns, it is better to use JUST one helper column to get appropriate row # for where the selected conditions are meet. So this way you can avoid excel to look for repeated process for matching row. Result will be higher performance. same for column numbers.

I have made quick sample file based on your file. Select a vehicle n C2 & tire type in C3. Also you are able to select appropriate heading in row 13.

If you convert your data to Table format, you can remove defined name for Sheet 1. So performance will be much higher.

 

[emgo]

Haseeb A,

That’s awesome, I’m amazed at your work and I’ll study what you did. I’m sure I will able to apply it another project. I attached an example of what I’m hoping to achieve with this Excel project. Take a look at the “Summary Table” in the Summary Sheet. I stared another thread about problem I’m having with this project concerning combining dates from 2 separate columns.



http://forum.chandoo.org/threads/add-a-column-to-min-offset-count-formula.32656/#post-193651



Thanks,

Ken