MATCH multiple names showing its row number's

[fpaul]

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)
[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date
[/TD]
[TD]Client
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/1/2018
[/TD]
[TD]NAME1
[/TD]
[TD]$100
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/1/2018
[/TD]
[TD]NAME2
[/TD]
[TD]$75
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/1/2018
[/TD]
[TD]NAME1
[/TD]
[TD]$65
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/2/2018
[/TD]
[TD]NAME1
[/TD]
[TD]$120
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/2/2018
[/TD]
[TD]NAME2
[/TD]
[TD]$200
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/2/2018
[/TD]
[TD]NAME1
[/TD]
[TD]$140
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/2/2018
[/TD]
[TD]NAME3
[/TD]
[TD]$120
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


DATA (tab)[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]Clients
[/TD]
[TD]JAN
[/TD]
[TD]FEB
[/TD]
[TD]MAR
[/TD]
[TD]APR
[/TD]
[TD]MAY
[/TD]
[TD]JUN
[/TD]
[TD]JUL
[/TD]
[TD]AUG
[/TD]
[TD]SEP
[/TD]
[TD]OCT
[/TD]
[TD]NOV
[/TD]
[TD]DEC
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NAME1
[/TD]
[TD]=MATCH(A3,January!$B$3:$B$600,0)+2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NAME2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NAME3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NAME4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NAME5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NAME6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NAME7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NAME8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NAME9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NAME10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Col Delane]

Howdy

Can I ask you to back-up a bit and explain what you're actually trying to achieve with this design (i.e. what's the purpose of doing this, why do you need this info in this way?) - for to me it looks rather complicated, and there is very likely to be a much easier, and more useful, way to achieve what you're ultimately after. Collating data on separate tabs for each month immediately makes data summarisation & analysis difficult (as you're experiencing), so a better method is to just have one sheet to hold ALL the data - as there are various Excel tools (formulas, Auto-filters, Advanced Filters, Pivot Tables, etc.) that can slice and dice data in a multitude of ways.

From looking at what you've described, my first thought is that you should merge all your month sheets into one database of transactions. This enables the entire list to be sorted, filtered, and summarised any which way you like. The most powerful & dynamic tool to summarise and/or produce reports/extracts from such data is the Pivot Table - which are not that difficult to set up for relatively simple data. You can then use the Pivot Table as your calculation engine, and even use it to quickly dump into a separate sheet all the transactions behind a selected summary result (no matter where that result was calculated in the PT)

Do you know about PTs and how to create one?

 

[fpaul]

Hi and thanks for info...
I have no idea what PT's are...
I actually had this sorted out... and the person who solved the problem did a very thorough job... as well as very quickly... the thread can be read here...
https://www.excelforum.com/excel-fo...le-names-showing-row-numbers.html#post4902842

 

[Col Delane]

Check out Pivot Tables - as mentioned, they are very powerful & dynamic tool for summarising and/or producing reports/extracts/charts from such data (and no formulas or VBA required!!). I think a PT would directly provide the info you're really after without the intermediate step of informing you as to which row/s to find it on in your month tabs.

Here's a good learning resource: http://www.contextures.com/pivottableindex.html