Forum Discussion
List of Items from Data Entry Form
Hi
I have a data entry sheet in the attached excel, i need to get list of department in another column whenever I enter department details in the data entry form automatically (by using formulas). Is there any method to bring this list without using any VBAs. (I dont want to used data/remove duplicates option- its not automatic)
Otherwise, is there any formula/trick can be used the function remove duplicate automatically and copy paste the list to another column?
For such layout
in E5 you may use
=IFERROR(INDEX(dataentry[Department],MATCH(0,INDEX(COUNTIF($E$4:$E4,dataentry[Department]),0),0)),"")and drag it down till the end of future range. With every new department in A it will appear in B.
15 Replies
- A pivot table or a Powerquery query comes to mind.
PT: Insert a pivot table and drag the field from which you need unique items to the row area.
PQ: Data, From Table, Delete Rows drop-down, Remove Duplicates, Close & Load, Close & Load to, select location.
Both options require you to push "Refresh All" before the list is updated.PT: I have to place this list in another calculation sheet, where i cannot apply pivot table option.
so that, every time I have to copy the list from PT and paste it to my calculation field.
What I need is a formula for automatic "remove duplicates" whenever i enter data in other column or sheet. Can it be done??
PQ: can you explain with a working?
Please, Sameer_Kuppanath_Sultan, attach a sample spreadsheet if you can do so without revealing any confidential information. It's a bit difficult to visualize what you're talking about from your brief description.
mathetes Whenever I add an item in Table A, (it may repeat) if it’s a new item it should list in Table B
I don’t want to use Pivot Table, since I have to place this list in another big calculation sheet.
Please see the attached sheet for your ref.
Sameer_Kuppanath_Sultan There is no reason why you wouldn't just place the pivottable on a separate worksheet.
