Hi Folk's, I am very limited in my knowledge with excel and could use some help for my business....
I have monthly entry's, each sheet named by month.... on a separate sheet, lets call it DATA, I have the whole year indexed for fast lookup.
I want to be able to find multiple MATCH's shown for each month showing its row number, separated by a comma, and showing all the multiple instances of a certain name, and all its row numbers in a single cell.
I was able to figure out how to find 1 instance of a name with this...
example: =MATCH(A1,JANUARY!$B$1:$B$1000,0)
this example returns the row number where to find the name I am looking for, which is great...
but if there's multiple entry's of this same name in the month, then I need it to show that same name and have it list all its row numbers.
for example if NAME1 is on row 191, then it will show: 191
this is good... but, I would like it to show more matches of the same name and its row number... in a SINGLE CELL.
for example : 191, 237, 278
This will help me quickly find NAME1 and its exact row number on the monthly sheet, and all the re-accruing instances of that same name.
I tried this: =MATCH(A1,JANUARY!$B$1:$B$1000,0)&","&MATCH(A1,JANUARY!$B$1:$B$100,0)
but it just shows the same NAME1 repeated... : 191,191
I should mention, on the DATA sheet, the 1st row has all the names list in column A,
columns B through M are the month's, which would contain the formula.
And to make it correctly show its proper row number if there's a header on the MONTHLY sheets... I had to add how many rows the header was... so,
if 2 top rows are used as a header, the list actually starts on row 3,
so I used this instead to show the proper location... : =MATCH(A1,JANUARY!$B$1:$B$1000,0)+2
this would correctly show its location for that month.
I greatly appreciate any suggestion or help with this.
PS: I don't have VBA
I'll try a small example here... this will show in the cell: 3
I would like it to show: 3, 5, 6, 8
January (tab)
<tbody>
</tbody>
DATA (tab)
<tbody>
</tbody>
I have monthly entry's, each sheet named by month.... on a separate sheet, lets call it DATA, I have the whole year indexed for fast lookup.
I want to be able to find multiple MATCH's shown for each month showing its row number, separated by a comma, and showing all the multiple instances of a certain name, and all its row numbers in a single cell.
I was able to figure out how to find 1 instance of a name with this...
example: =MATCH(A1,JANUARY!$B$1:$B$1000,0)
this example returns the row number where to find the name I am looking for, which is great...
but if there's multiple entry's of this same name in the month, then I need it to show that same name and have it list all its row numbers.
for example if NAME1 is on row 191, then it will show: 191
this is good... but, I would like it to show more matches of the same name and its row number... in a SINGLE CELL.
for example : 191, 237, 278
This will help me quickly find NAME1 and its exact row number on the monthly sheet, and all the re-accruing instances of that same name.
I tried this: =MATCH(A1,JANUARY!$B$1:$B$1000,0)&","&MATCH(A1,JANUARY!$B$1:$B$100,0)
but it just shows the same NAME1 repeated... : 191,191
I should mention, on the DATA sheet, the 1st row has all the names list in column A,
columns B through M are the month's, which would contain the formula.
And to make it correctly show its proper row number if there's a header on the MONTHLY sheets... I had to add how many rows the header was... so,
if 2 top rows are used as a header, the list actually starts on row 3,
so I used this instead to show the proper location... : =MATCH(A1,JANUARY!$B$1:$B$1000,0)+2
this would correctly show its location for that month.
I greatly appreciate any suggestion or help with this.
PS: I don't have VBA
I'll try a small example here... this will show in the cell: 3
I would like it to show: 3, 5, 6, 8
January (tab)
A | B | ||||||||
Date | Client | ||||||||
1/1/2018 | NAME1 | $100 | |||||||
1/1/2018 | NAME2 | $75 | |||||||
1/1/2018 | NAME1 | $65 | |||||||
1/2/2018 | NAME1 | $120 | |||||||
1/2/2018 | NAME2 | $200 | |||||||
1/2/2018 | NAME1 | $140 | |||||||
1/2/2018 | NAME3 | $120 |
<tbody>
</tbody>
DATA (tab)
Clients | JAN | FEB | MAR | APR | MAY | JUN | JUL | AUG | SEP | OCT | NOV | DEC |
NAME1 | =MATCH(A3,January!$B$3:$B$600,0)+2 | |||||||||||
NAME2 | ||||||||||||
NAME3 | ||||||||||||
NAME4 | ||||||||||||
NAME5 | ||||||||||||
NAME6 | ||||||||||||
NAME7 | ||||||||||||
NAME8 | ||||||||||||
NAME9 | ||||||||||||
NAME10 |
<tbody>
</tbody>