I have a data sheet with 100 companies data, very huge one, and day by day I need to enter some companies data.
After that I need to copy each and every company data into separate sheet line by line. Can I get any formula to copy each company rows into separate sheet. Plz see attachment for example.
Thank you.
How much control do you have over the Data Sheet? It has merged cells which are bad news in Excel. They look very pretty but are a pain in the neck and other parts lower down to work with. They prohibit you from doing a lot of things so you have to detect them, unmerged them and remerge them before you can do anything with them.
I could think of a very fast and efficient way of doing this using pivot tables. However the merged cells don't support pivot tables. Also having the date between the column headers and the data means extra work.
So step one is to reformat the existing data before we can even start working with it. So if you have control over the input, remove the merge on columns B:C - if you need the extra space to read the supplier, just make the cell wider. Also Columns E and G are blank, so don't include them at all.
Finally, get rid of the AUGUST '16 or move it to the first row and put the header rows next to the data.
Otherwise, we'll have to do all this cleanup before we can even start the program or use a less-efficient method (looping rather than range copy) to parse the data.
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
Removing the merged cells as per Dflak's recommendations:
in B2 of Suplplier sheets
=TRIM(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255))
in A3
=IFERROR(INDEX('DATA SHEET'!$A$3:$K$13,SMALL(IF('DATA SHEET'!$B$3:$B$13=$B$2,ROW('DATA SHEET'!$B$3:$B$13)-ROW($B$3)+1,""),ROWS($B$3:B3)),COLUMN()),"")
Enter with Ctrl+Shift+Enter
Copy across and down as far as required.
I ignored AUGUST '16 as I was not sure of its purpose
Sorry for that dflak. Next time I will remember this one.
JohnTopley, thank you very much for your support.
I want one more extension......
If I enter more details in DATA sheet, It was not coming in supplier sheet automatically. Can you plz edit above formula according to that. Otherwise every day I want to update each and every sheet of suppliers.
Thank you.
Just change the range to your likely maximum
=IFERROR(INDEX('DATA SHEET'!$A$3:$K$10000,SMALL(IF('DATA SHEET'!$B$3:$B$10000=$B$2,ROW('DATA SHEET'!$B$3:$B$10000)-ROW($B$3)+1,""),ROWS($B$3:B3)),COLUMN()),"")
Thank you very much........
one more is generally we divide this form as monthly transations
like AUGUST '16 , SEPTEMBER '16,
So, If we enter month in DATA sheet, then it will appear in all supplier sheet.
please see attachment
Not about month, If we enter any text between rows, then it will appear in all suppliers sheets.
I don't how you do this using a formula as you effectively have monthly sheets within your "master" sheet: you might have resort to VBA to enable this.
Last edited by JohnTopley; 09-23-2016 at 01:12 PM.
Ok, thank you very much.
See attached:
Run macros from "Tables"
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks